Home |
Search |
Today's Posts |
#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(A2<0,IF(A3<0,((A2+A3)/2),A2),IF(A3<0,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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to combine conditional format with formula ? | Excel Worksheet Functions | |||
Office2000: Conditional format behaves strangely | Excel Discussion (Misc queries) | |||
conditional formatting formula | Excel Discussion (Misc queries) | |||
Formula Dependant Conditional Formatting | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions |