#1
|
|||
|
|||
Huge Stdev bars using if-array
Small dataset with 3 dataseries in 2 criteria (columns),
Stdev values looks absolutely errornous when using array fiunction compared to single range formulas. 1 Excel attached with 2 conditions in same tab. Need to use array as this is part of huge dataset. Cannot find problem Can someone help me out please? R |
#2
|
|||
|
|||
Are you talking about the formulas in columns AI:AO, rows 9, 11 and 13.
What is the problem. What problem is it that you can't find. What do you mean more precisely by a huge dataset? |
#3
|
|||
|
|||
Hi XOR. Indeed, formulas in mentioned cells (AI:AO, R9-11-13) gives STDEV values much different from the 'real' stdev as mentioned in cells N:S, R 9-13. The real ones are based on a simple stdev formula from a particlar range while the former ones are based on a formula using criteri (B, H, 1, 6). I gues there must be something wrong with either formula or formatting of particular empty cells?
(The dataset in the Excel file is just a small piece of a big dataset, thats what I meant with huge) R |
#4
|
|||
|
|||
Take a look here.
If you try for example the following formula: =LEN(Z4) you will get the result 1. I don't know what you have in Z4 (and others), but try to select X4:AD12, enter 1, hold down Ctrl (to put 1 in all those cells) then delete them again, and you should see TRUE in U4:U12 and you should see other StDev's. Why I asked about the huge dataset was because it may be a problem with array formulas. Last edited by xor; 02-02-2019 at 11:42 PM. |
#5
|
|||
|
|||
Agree but Stdev values are still very high and very different compared to the ones in cells N:S, R 9-13. There must be sonmething else wrong I guess.
|
#6
|
|||
|
|||
What do you mean by still very high. Do you mean 0.08618 as calculated in AK9 is too high?
If you do as I mentioned in #4 you will get StDev between 0.06895 and 0.86500 |
#7
|
|||
|
|||
Do you think the values in Sheet2!N3:T5 are wrong?
|
#8
|
|||
|
|||
correct, but this is when you exclude the blank cells! when you include blanks, SD will be 10x higher!
|
#9
|
|||
|
|||
Degfinitely. The ones in N:S, R 9-13 are correct, I calculated these by hand as well.
|
#10
|
|||
|
|||
I don't know what kind of dirt you have in sheet test, but if you can't use what I made in Sheet2 then I give up.
|
#11
|
|||
|
|||
agree. File may be corrupt, thanx R
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Huge Mistake! | markg2 | Excel | 2 | 05-12-2014 08:05 AM |
Convert String Array to Integer Array from a User Input? | tinfanide | Excel Programming | 4 | 12-26-2012 08:56 PM |
names on bars | Psychopig7 | Project | 1 | 06-22-2012 04:58 AM |
STDEV minimum number of points | littlepeaks | Excel | 0 | 01-15-2012 06:40 PM |
Tool bars gone | wasioja | Word | 1 | 05-21-2009 11:00 AM |