Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVG or Return 1 value from either of 2 cells if one is blank
Hi everyone,
I'm using the following formula to average the values of 2 cells. If either is empty, I want it to return the value in the non-blank cell. =IF(AND(E80,H80),AVERAGE(E8,H8),SUM(E8,H8)) This works fine but is there a better way to do it? Cheers -- Diddy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVG or Return 1 value from either of 2 cells if one is blank
Here's another way (avoiding the IF):
=SUM(E8,H8)/COUNT(E8,H8) Hope this helps. Pete On Feb 18, 11:00*am, Diddy wrote: Hi everyone, I'm using the following formula to average the values of 2 cells. If either is empty, I want it to return the value in the non-blank cell. =IF(AND(E80,H80),AVERAGE(E8,H8),SUM(E8,H8)) This works fine but is there a better way to do it? Cheers -- Diddy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVG or Return 1 value from either of 2 cells if one is blank
Hi,
Average ignores blanks cell so =AVERAGE(E8,H8) does what you want. Mike "Diddy" wrote: Hi everyone, I'm using the following formula to average the values of 2 cells. If either is empty, I want it to return the value in the non-blank cell. =IF(AND(E80,H80),AVERAGE(E8,H8),SUM(E8,H8)) This works fine but is there a better way to do it? Cheers -- Diddy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVG or Return 1 value from either of 2 cells if one is blank
Hi Mike,
Sorry Mike, I've misled you! The data is coming in from worksheets prepared by different people from a database. I've no control over what is put in the db fields, so in some cases the missing data may be left blank or may be a zero. The formula I have does work but I wondered if there was a more efficient way. Cheers -- Diddy "Mike H" wrote: Hi, Average ignores blanks cell so =AVERAGE(E8,H8) does what you want. Mike "Diddy" wrote: Hi everyone, I'm using the following formula to average the values of 2 cells. If either is empty, I want it to return the value in the non-blank cell. =IF(AND(E80,H80),AVERAGE(E8,H8),SUM(E8,H8)) This works fine but is there a better way to do it? Cheers -- Diddy |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVG or Return 1 value from either of 2 cells if one is blank
Hi Pete,
Sorry MY fault. I did not phrase the question or the subject line correctly. E8 or H8 can be either a value 0, 0 or blank. If both are a value then I need the avg. If either one is blank or 0 then I need the other value returned. Thanks to both you and Mike for replying and sorry again :-) -- Diddy "Pete_UK" wrote: Here's another way (avoiding the IF): =SUM(E8,H8)/COUNT(E8,H8) Hope this helps. Pete On Feb 18, 11:00 am, Diddy wrote: Hi everyone, I'm using the following formula to average the values of 2 cells. If either is empty, I want it to return the value in the non-blank cell. =IF(AND(E80,H80),AVERAGE(E8,H8),SUM(E8,H8)) This works fine but is there a better way to do it? Cheers -- Diddy |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVG or Return 1 value from either of 2 cells if one is blank
Hi,
It's irrelevant where the data are coming from. If your formula works well then put my formula alongside yours is a cell and try and make them give different answers and you'll fail to do so because they will always return the same one. Including #DIV/0 of both cells are empty. You asked for a simpler way and as of yet the one I posted is the simplest and meets all of the requirements Best regards, Mike "Diddy" wrote: Hi Mike, Sorry Mike, I've misled you! The data is coming in from worksheets prepared by different people from a database. I've no control over what is put in the db fields, so in some cases the missing data may be left blank or may be a zero. The formula I have does work but I wondered if there was a more efficient way. Cheers -- Diddy "Mike H" wrote: Hi, Average ignores blanks cell so =AVERAGE(E8,H8) does what you want. Mike "Diddy" wrote: Hi everyone, I'm using the following formula to average the values of 2 cells. If either is empty, I want it to return the value in the non-blank cell. =IF(AND(E80,H80),AVERAGE(E8,H8),SUM(E8,H8)) This works fine but is there a better way to do it? Cheers -- Diddy |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVG or Return 1 value from either of 2 cells if one is blank
Hi Mike,
I seem to have lost the gift of communication today. Not meaning to be rude at all and very grateful for reponses. What I meant to convey (and not succeeding at all) is that in the original Q and the subject line, I've asked for ways to deal with blanks but the value can be 0 if there is a score for the test, or 0 or blank. 0 or blank is if the test was missed (what I was trying to say when waffling about data being from DB, was that if we could control the input at the db stage then that would be a good solution). Sorry again :-) -- Diddy "Mike H" wrote: Hi, It's irrelevant where the data are coming from. If your formula works well then put my formula alongside yours is a cell and try and make them give different answers and you'll fail to do so because they will always return the same one. Including #DIV/0 of both cells are empty. You asked for a simpler way and as of yet the one I posted is the simplest and meets all of the requirements Best regards, Mike "Diddy" wrote: Hi Mike, Sorry Mike, I've misled you! The data is coming in from worksheets prepared by different people from a database. I've no control over what is put in the db fields, so in some cases the missing data may be left blank or may be a zero. The formula I have does work but I wondered if there was a more efficient way. Cheers -- Diddy "Mike H" wrote: Hi, Average ignores blanks cell so =AVERAGE(E8,H8) does what you want. Mike "Diddy" wrote: Hi everyone, I'm using the following formula to average the values of 2 cells. If either is empty, I want it to return the value in the non-blank cell. =IF(AND(E80,H80),AVERAGE(E8,H8),SUM(E8,H8)) This works fine but is there a better way to do it? Cheers -- Diddy |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVG or Return 1 value from either of 2 cells if one is blank
Hi,
Now that's a different question. Not necessarily better then your but another way =AVERAGE(IF(ISNUMBER(MATCH(COLUMN(E8:H8),{5,8},0)) *(E8:H80),E8:H8)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "Diddy" wrote: Hi Mike, I seem to have lost the gift of communication today. Not meaning to be rude at all and very grateful for reponses. What I meant to convey (and not succeeding at all) is that in the original Q and the subject line, I've asked for ways to deal with blanks but the value can be 0 if there is a score for the test, or 0 or blank. 0 or blank is if the test was missed (what I was trying to say when waffling about data being from DB, was that if we could control the input at the db stage then that would be a good solution). Sorry again :-) -- Diddy "Mike H" wrote: Hi, It's irrelevant where the data are coming from. If your formula works well then put my formula alongside yours is a cell and try and make them give different answers and you'll fail to do so because they will always return the same one. Including #DIV/0 of both cells are empty. You asked for a simpler way and as of yet the one I posted is the simplest and meets all of the requirements Best regards, Mike "Diddy" wrote: Hi Mike, Sorry Mike, I've misled you! The data is coming in from worksheets prepared by different people from a database. I've no control over what is put in the db fields, so in some cases the missing data may be left blank or may be a zero. The formula I have does work but I wondered if there was a more efficient way. Cheers -- Diddy "Mike H" wrote: Hi, Average ignores blanks cell so =AVERAGE(E8,H8) does what you want. Mike "Diddy" wrote: Hi everyone, I'm using the following formula to average the values of 2 cells. If either is empty, I want it to return the value in the non-blank cell. =IF(AND(E80,H80),AVERAGE(E8,H8),SUM(E8,H8)) This works fine but is there a better way to do it? Cheers -- Diddy |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVG or Return 1 value from either of 2 cells if one is blank
Hi Mike,
Yes that does the trick! I'll probably stick with the original but learning alternative ways helps more than you will know :-) Thank you -- Diddy "Mike H" wrote: Hi, Now that's a different question. Not necessarily better then your but another way =AVERAGE(IF(ISNUMBER(MATCH(COLUMN(E8:H8),{5,8},0)) *(E8:H80),E8:H8)) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "Diddy" wrote: Hi Mike, I seem to have lost the gift of communication today. Not meaning to be rude at all and very grateful for reponses. What I meant to convey (and not succeeding at all) is that in the original Q and the subject line, I've asked for ways to deal with blanks but the value can be 0 if there is a score for the test, or 0 or blank. 0 or blank is if the test was missed (what I was trying to say when waffling about data being from DB, was that if we could control the input at the db stage then that would be a good solution). Sorry again :-) -- Diddy "Mike H" wrote: Hi, It's irrelevant where the data are coming from. If your formula works well then put my formula alongside yours is a cell and try and make them give different answers and you'll fail to do so because they will always return the same one. Including #DIV/0 of both cells are empty. You asked for a simpler way and as of yet the one I posted is the simplest and meets all of the requirements Best regards, Mike "Diddy" wrote: Hi Mike, Sorry Mike, I've misled you! The data is coming in from worksheets prepared by different people from a database. I've no control over what is put in the db fields, so in some cases the missing data may be left blank or may be a zero. The formula I have does work but I wondered if there was a more efficient way. Cheers -- Diddy "Mike H" wrote: Hi, Average ignores blanks cell so =AVERAGE(E8,H8) does what you want. Mike "Diddy" wrote: Hi everyone, I'm using the following formula to average the values of 2 cells. If either is empty, I want it to return the value in the non-blank cell. =IF(AND(E80,H80),AVERAGE(E8,H8),SUM(E8,H8)) This works fine but is there a better way to do it? Cheers -- Diddy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index/match - make blank cells return a blank value. | Excel Worksheet Functions | |||
Nested IF - return a blank when compared cells are blank | New Users to Excel | |||
Return All Non-Blank Cells | Excel Worksheet Functions | |||
Need Vlookup to return a value of $0.00 in blank cells | Excel Worksheet Functions | |||
Need Vlookup to return a value of $0.00 in blank cells | Excel Worksheet Functions |