Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Percentage Breakdown Formula Help?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Percentage Breakdown Formula Help?
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" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Percentage Breakdown Formula Help?
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" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Percentage Breakdown Formula Help?
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" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Percentage Breakdown Formula Help?
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" wrote: 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" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Percentage Breakdown Formula Help?
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 --- "Dan the Man" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
anyone know how to breakdown a paragraph to a spreadsheet | Excel Worksheet Functions | |||
Monthly Budget Breakdown | Excel Discussion (Misc queries) | |||
Breakdown | Excel Worksheet Functions | |||
How 2 breakdown $$$ into $ $ $? | Excel Discussion (Misc queries) | |||
Breakdown of data in a bar chart | Charts and Charting in Excel |