Percentage Breakdown Formula Help?

[Dan the Man]

I have a spreadsheet that I'm using to record statistics. I grab those
statistics from another sheet entitled "Raw Data". The following fomula
provides me with raw score data for all outcomes in Column V that are 91% or
greater:

=SUMPRODUCT(--('Raw Data'!A4:A500>=DATE(2008,1,1)),--('Raw
Data'!A4:A500<=DATE(2008,12,31)),--( 'Raw Data'!V4:V500>=91%))

The problem I am having is obtaining the raw score data for other percentage
range breakdowns: 71 to 90%, 51 to 70%, 31 to 50%, 11 to 30%, and 0 to 10%.

If someone could kindly provide me with one sample (e.g. 71 to 90%) I think
I could take it from there. The formula I tried (without success) was:

=SUMPRODUCT(--('Raw Data'!A4:A500>=DATE(2008,1,1)),--('Raw
Data'!A4:A500<=DATE(2008,12,31)),--('Raw Data'!V4:V500>=71%))--('Raw
Data'!V4:V500<=90%)

The formula didn't produce an error, but when I went to test it, I did not
get the correct results. Currently I have one score at 97%, and this is
accurately identified (per the first formula above). However I do NOT have
any scores between 71 to 90%, but the second formula above tells me that I
have "1" outcome in this range. As such I know that the formula I wrote isn't
reflecting the information correctly, because the correct outcome should be
"0", and not "1". Any help would be appreciated.

Thanks much,

Dan

 

[Max]

Think this slightly corrected version of your 2nd formula should do it:
=SUMPRODUCT(--('Raw Data'!A4:A500>=DATE(2008,1,1)),--('Raw
Data'!A4:A500<=DATE(2008,12,31)),--('Raw Data'!V4:V500>=71%),--('Raw
Data'!V4:V500<=90%))

Should you still have issues, then maybe you could use ROUND on the source
data's col V, like this, to make the returns consistent with the set limits:
=SUMPRODUCT(--('Raw Data'!A4:A500>=DATE(2008,1,1)),--('Raw
Data'!A4:A500<=DATE(2008,12,31)),--(ROUND('Raw
Data'!V4:V500,2)>=71%),--(ROUND('Raw Data'!V4:V500,2)<=90%))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik

 

[Dan the Man]

Thanks Max, your first formula idea worked. Guess I was almost there! I
appreciate it. The only strange outcome I am now gettingn (didn't see this
before), is with my first formula (the one which tells me how many outcomes
are at 91% or greater).

When I enter an admission date in Column A (e.g. Jul 1, 2008), for some odd
reason, the cell which reflects percentage outcomes at 91% or greater (Column
V) generates a "1". I know this is not correct, because, I should not get ANY
percentage outcomes until I begin to populate column V with percentages. No
other percentage range populates (except 91% or greater), but once a date in
Column A is identifed, a "1" appears next to outcomes that are 91% or
greater. Here is my initial formula which seems to be causing the problem:

=SUMPRODUCT(--('Raw Data'!A4:A500>=DATE(2008,1,1)),--('Raw
Data'!A4:A500<=DATE(2008,12,31)),--('Raw Data'!V4:V500>=91%))

Any suggestions would be greatly appreciated.........Best, Dan

 

[Max]

Maybe try adding an ISNUMBER check on that col V*:
=SUMPRODUCT(--('Raw Data'!A4:A500>=DATE(2008,1,1)),--('Raw
Data'!A4:A500<=DATE(2008,12,31)),--('Raw Data'!V4:V500>=91%),--(ISNUMBER('Raw
Data'!V4:V500)))

*the spurious return could be caused by text or a null string returned in
col V's formulas
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik

 

[Dan the Man]

That WORKED Max! Thank you so very much. All of the other percentage
breakdown parameters are working just fine (e.g. 71-90%, etc), without the
need for the ISNUMBER check. Interestingly I tried adding the ISNUMBER check
to these other percentage breakdowns, just to see what would happen, and the
result created skewing. It only seems to be necessary for the 91% or greater
formula. I can't seem to figure out why one formula needs the ISNUMBER check
to function appropriately and the others do not?

Max you always provide me with great solutions when my head can't seem to
figure it out............Always appreciated!

Dan

 

[Max]

Welcome, Dan. In the other formulas, there are already 2 col V terms which
taken together, produces the resultant 1/0 array for the numeric percentage
range (eg: >=71%, <=90%). In the earlier, there was only one col V term
(>=91%) which hence left it "vulnerable" to Excel's interp on text/null
strings being greater than any real number.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik