Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 02-02-2019, 07:51 AM
Intruder Intruder is offline Windows XP Office 2007
Advanced Beginner
 
Join Date: Dec 2010
Posts: 49
Intruder is on a distinguished road
Default 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
Attached Files
File Type: xlsx Test excel stdev array.xlsx (45.7 KB, 4 views)
Reply With Quote
  #2  
Old 02-02-2019, 11:58 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,005
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default

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?
Reply With Quote
  #3  
Old 02-02-2019, 07:06 PM
Intruder Intruder is offline Windows XP Office 2007
Advanced Beginner
 
Join Date: Dec 2010
Posts: 49
Intruder is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 02-02-2019, 09:12 PM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,005
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default

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.
Attached Files
File Type: xlsx Test excel stdev array_2.xlsx (31.4 KB, 11 views)

Last edited by xor; 02-02-2019 at 11:42 PM.
Reply With Quote
  #5  
Old 02-03-2019, 02:36 AM
Intruder Intruder is offline Windows XP Office 2007
Advanced Beginner
 
Join Date: Dec 2010
Posts: 49
Intruder is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 02-03-2019, 03:21 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,005
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default

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
Reply With Quote
  #7  
Old 02-03-2019, 04:39 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,005
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default

Do you think the values in Sheet2!N3:T5 are wrong?
Attached Files
File Type: xlsx Test excel stdev array_3.xlsx (41.9 KB, 2 views)
Reply With Quote
  #8  
Old 02-03-2019, 04:42 AM
Intruder Intruder is offline Windows XP Office 2007
Advanced Beginner
 
Join Date: Dec 2010
Posts: 49
Intruder is on a distinguished road
Default

correct, but this is when you exclude the blank cells! when you include blanks, SD will be 10x higher!
Reply With Quote
  #9  
Old 02-03-2019, 05:15 AM
Intruder Intruder is offline Windows XP Office 2007
Advanced Beginner
 
Join Date: Dec 2010
Posts: 49
Intruder is on a distinguished road
Default

Degfinitely. The ones in N:S, R 9-13 are correct, I calculated these by hand as well.
Reply With Quote
  #10  
Old 02-03-2019, 06:05 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,005
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default

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.
Reply With Quote
  #11  
Old 02-03-2019, 06:35 AM
Intruder Intruder is offline Windows XP Office 2007
Advanced Beginner
 
Join Date: Dec 2010
Posts: 49
Intruder is on a distinguished road
Default

agree. File may be corrupt, thanx R
Reply With Quote
Reply

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 03:05 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft