Microsoft Office Forums

Go Back   Microsoft Office Forums > >
Reload this Page Huge Stdev bars using if-array
Register FAQ Mark Forums Read

Reply
 
Thread Tools Display Modes
  #1  
Old 02-02-2019, 07:51 AM
Intruder Intruder is offline Huge Stdev bars using if-array Windows XP Huge Stdev bars using if-array Office 2007
Advanced Beginner
Huge Stdev bars using if-array 		 
Join Date: Dec 2010
Posts: 59
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, 10 views)
Reply With Quote
  #2  
Old 02-02-2019, 11:58 AM
xor xor is offline Huge Stdev bars using if-array Windows 10 Huge Stdev bars using if-array Office 2016
Expert
  
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
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 Huge Stdev bars using if-array Windows XP Huge Stdev bars using if-array Office 2007
Advanced Beginner
Huge Stdev bars using if-array 		 
Join Date: Dec 2010
Posts: 59
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 Huge Stdev bars using if-array Windows 10 Huge Stdev bars using if-array Office 2016
Expert
  
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
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, 17 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 Huge Stdev bars using if-array Windows XP Huge Stdev bars using if-array Office 2007
Advanced Beginner
Huge Stdev bars using if-array 		 
Join Date: Dec 2010
Posts: 59
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 Huge Stdev bars using if-array Windows 10 Huge Stdev bars using if-array Office 2016
Expert
  
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
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 Huge Stdev bars using if-array Windows 10 Huge Stdev bars using if-array Office 2016
Expert
  
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
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, 8 views)
Reply With Quote
  #8  
Old 02-03-2019, 04:42 AM
Intruder Intruder is offline Huge Stdev bars using if-array Windows XP Huge Stdev bars using if-array Office 2007
Advanced Beginner
Huge Stdev bars using if-array 		 
Join Date: Dec 2010
Posts: 59
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 Huge Stdev bars using if-array Windows XP Huge Stdev bars using if-array Office 2007
Advanced Beginner
Huge Stdev bars using if-array 		 
Join Date: Dec 2010
Posts: 59
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 Huge Stdev bars using if-array Windows 10 Huge Stdev bars using if-array Office 2016
Expert
  
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
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 Huge Stdev bars using if-array Windows XP Huge Stdev bars using if-array Office 2007
Advanced Beginner
Huge Stdev bars using if-array 		 
Join Date: Dec 2010
Posts: 59
Intruder is on a distinguished road
Default
agree. File may be corrupt, thanx R
Reply With Quote
Reply

« Previous Thread | Next Thread »
Thread Tools
Display Modes
Linear Mode Linear Mode


Similar Threads
Thread Thread Starter Forum Replies Last Post
Huge Mistake! markg2 Excel 2 05-12-2014 08:05 AM
Huge Stdev bars using if-array 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

Other Forums: Access Forums

All times are GMT -7. The time now is 08:33 PM.

Contact Us - Privacy Statement - Top

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft