Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with Averages/Sums of values returned from IF function
Any help on this topic is appreciated, obviously.
I'm working in Excel, and I want to find an average of a range of 5 cells. The thing is, that not all of these 5 cells have a value greater than 0, and I only want the average of the cells greater than 0. On top of that, these cells are all returned from a nested IF function that returns the values 0, 1, or 2 depending on the result of the IF's. I'm guessing that because the value's of 0, 1, and 2 are returned from the IF function are formatted as text, because I've tried to do an AVERAGE function, and I've also tried to do a SUM combined with a COUNTIF(to count those greater than 0, to assist with the average) and none of those are returning anything. (The AVERAGE function returns a divided by 0 error, and the SUM/COUNTIF both return 0 as a value). I'm confident that this can be done, but I'm either over thinking it or just plain don't know how to do what I'm trying to do. Any help is appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with Averages/Sums of values returned from IF function
I forgot to mention that this is on Excel 2003.
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with Averages/Sums of values returned from IF function
Try =AVERAGE(IF(A1:A50,A1:A5))
but it is an array formula so you must commit it with CTRL+SHIFT+ENTER not just ENTER. You will know if you did it correctly if you see that Excel has added braces { } around your formula. This non-array formula will also work =SUM(A1:A5)/COUNTIF(A1:A5,"0") A third alternative is to change the IF formula. So, for example, rather than =IF(A1<5, 0, A1*3) use =IF(A1<5, "", A1*3). That is a pair of quotes (") in place of the zero. Then simple AVERAGE formula will work for you best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "TheBigUnit622" wrote in message ... Any help on this topic is appreciated, obviously. I'm working in Excel, and I want to find an average of a range of 5 cells. The thing is, that not all of these 5 cells have a value greater than 0, and I only want the average of the cells greater than 0. On top of that, these cells are all returned from a nested IF function that returns the values 0, 1, or 2 depending on the result of the IF's. I'm guessing that because the value's of 0, 1, and 2 are returned from the IF function are formatted as text, because I've tried to do an AVERAGE function, and I've also tried to do a SUM combined with a COUNTIF(to count those greater than 0, to assist with the average) and none of those are returning anything. (The AVERAGE function returns a divided by 0 error, and the SUM/COUNTIF both return 0 as a value). I'm confident that this can be done, but I'm either over thinking it or just plain don't know how to do what I'm trying to do. Any help is appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with Averages/Sums of values returned from IF function
I have re-read your message! You say SUM(A1:A5) returns zero! Copy and Paste
one of your IF formulas in a reply to this message - it seems you are getting text rather than numbers. -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "TheBigUnit622" wrote in message ... Any help on this topic is appreciated, obviously. I'm working in Excel, and I want to find an average of a range of 5 cells. The thing is, that not all of these 5 cells have a value greater than 0, and I only want the average of the cells greater than 0. On top of that, these cells are all returned from a nested IF function that returns the values 0, 1, or 2 depending on the result of the IF's. I'm guessing that because the value's of 0, 1, and 2 are returned from the IF function are formatted as text, because I've tried to do an AVERAGE function, and I've also tried to do a SUM combined with a COUNTIF(to count those greater than 0, to assist with the average) and none of those are returning anything. (The AVERAGE function returns a divided by 0 error, and the SUM/COUNTIF both return 0 as a value). I'm confident that this can be done, but I'm either over thinking it or just plain don't know how to do what I'm trying to do. Any help is appreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with Averages/Sums of values returned from IF function
Assuming your values are in A1:E1..
Try =AVERAGE((A1:E1)*1) IMP: Press CTRL-SHIFT-ENTER after typing/pasting as these are Array formulas. It will give you average of 5 values and will count 0 as a value. If you want average of non-zero values then use =AVERAGE(IF((A1:E1)*10,(A1:E1)*1,"")) "TheBigUnit622" wrote: Any help on this topic is appreciated, obviously. I'm working in Excel, and I want to find an average of a range of 5 cells. The thing is, that not all of these 5 cells have a value greater than 0, and I only want the average of the cells greater than 0. On top of that, these cells are all returned from a nested IF function that returns the values 0, 1, or 2 depending on the result of the IF's. I'm guessing that because the value's of 0, 1, and 2 are returned from the IF function are formatted as text, because I've tried to do an AVERAGE function, and I've also tried to do a SUM combined with a COUNTIF(to count those greater than 0, to assist with the average) and none of those are returning anything. (The AVERAGE function returns a divided by 0 error, and the SUM/COUNTIF both return 0 as a value). I'm confident that this can be done, but I'm either over thinking it or just plain don't know how to do what I'm trying to do. Any help is appreciated. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with Averages/Sums of values returned from IF function
Thanks for your help so far, Bernard. Seems like you know what I'm trying to
accomplish. One of the IF statements follows (Stats and Points is another sheet in the workbook). =IF('Stats and Points'!F2=60, "2", IF('Stats and Points'!F2<=44.9, "0", "1")) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with Averages/Sums of values returned from IF function
Are you sure that you want text strings as the results of your formula? If
you want numbers, throw away the quote marks. -- David Biddulph "TheBigUnit622" wrote in message ... Thanks for your help so far, Bernard. Seems like you know what I'm trying to accomplish. One of the IF statements follows (Stats and Points is another sheet in the workbook). =IF('Stats and Points'!F2=60, "2", IF('Stats and Points'!F2<=44.9, "0", "1")) |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with Averages/Sums of values returned from IF function
Thanks to all three of you for the help. It's greatly appreciated.
-- Custom Signature Here. "TheBigUnit622" wrote: Any help on this topic is appreciated, obviously. I'm working in Excel, and I want to find an average of a range of 5 cells. The thing is, that not all of these 5 cells have a value greater than 0, and I only want the average of the cells greater than 0. On top of that, these cells are all returned from a nested IF function that returns the values 0, 1, or 2 depending on the result of the IF's. I'm guessing that because the value's of 0, 1, and 2 are returned from the IF function are formatted as text, because I've tried to do an AVERAGE function, and I've also tried to do a SUM combined with a COUNTIF(to count those greater than 0, to assist with the average) and none of those are returning anything. (The AVERAGE function returns a divided by 0 error, and the SUM/COUNTIF both return 0 as a value). I'm confident that this can be done, but I'm either over thinking it or just plain don't know how to do what I'm trying to do. Any help is appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup multiple values - sum returned values into single cell | Excel Worksheet Functions | |||
More Questions Sums and Averages by Date | Excel Discussion (Misc queries) | |||
Getting Sums of Values | Excel Worksheet Functions | |||
How do I sum values returned from functions? | Excel Worksheet Functions | |||
LOOKUP FUNCTION WITH SUMS VALUES | Excel Discussion (Misc queries) |