If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional Formula
Can anyone help with the following: -
I have data in cells A2 and A3, in another cell (A10) I need a formula so that If there are no values in A2 or A3 the value shown in A10 will be zero and if there are values in both A2 and A3, A10 will show the average of A2 and A3 (as in (A2+A3)/2) but if there is a value in A2 and no value in A3 (or visa versa), A10 shows the value in A2 (or A3) without entering the average of the two cells. Regards, Brendan |
#2
|
|||
|
|||
Try this, assuming you are using number values in your cells, ie: using
a zero (0) instead of an empty cell. =IF(A20,IF(A30,((A2+A3)/2),A2),IF(A30,A3,0)) No doubt there is a more elegant solution, but I'm pretty crap with maths and formula logic... well, and Excel formulas in general. It seemed to work for me though! Ell |
#3
|
|||
|
|||
simple use the formula:
=AVERAGE(A2:A3) Your formula (A2+A3)/2 considers empty cells as legal values. Whereas the AVERAGE function does not consider blank cells. - Mangesh "Brendan J Cuffe" wrote in message ... Can anyone help with the following: - I have data in cells A2 and A3, in another cell (A10) I need a formula so that If there are no values in A2 or A3 the value shown in A10 will be zero and if there are values in both A2 and A3, A10 will show the average of A2 and A3 (as in (A2+A3)/2) but if there is a value in A2 and no value in A3 (or visa versa), A10 shows the value in A2 (or A3) without entering the average of the two cells. Regards, Brendan |
#4
|
|||
|
|||
=IF(AND(A2="",A3=""),"",AVERAGE(A2:A3))
-- HTH RP (remove nothere from the email address if mailing direct) "Brendan J Cuffe" wrote in message ... Can anyone help with the following: - I have data in cells A2 and A3, in another cell (A10) I need a formula so that If there are no values in A2 or A3 the value shown in A10 will be zero and if there are values in both A2 and A3, A10 will show the average of A2 and A3 (as in (A2+A3)/2) but if there is a value in A2 and no value in A3 (or visa versa), A10 shows the value in A2 (or A3) without entering the average of the two cells. Regards, Brendan |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Conditional formatting on cells with a VLOOKUP formula in them | JenniM | General Discussion | 4 | April 1st, 2005 06:45 PM |
How do I copy a conditional format that uses a formula in a serie. | TygerJ | Setting up and Configuration | 1 | March 31st, 2005 01:07 AM |
Conditional Formatting on cell with Formula | AGnes | General Discussion | 2 | September 22nd, 2004 02:41 AM |
#DIV/0! error - trying to make formula conditional | christopherp | Worksheet Functions | 5 | August 5th, 2004 06:05 PM |