Applying formula on formula

A

Aditya Kumar

I have used IF formula on 10 grid values to get desried values.Further, on
these values i want to apply average formula.
Whenever i apply the average onto the grid values, it show DIV#0.
I have to maually write the IF derived values in new column and apply the
average formula onto them. Please help.
 
R

Roger Govier

Hi

Show some examples of the If formulae you are using, and the formula you are
using to calculate the average.
 
A

Aditya Kumar

Hi Roger,

I'm subtracting two columns to get intial value. On this intial value i am
applying IF formula which is as follows-
IF(I3<=0,"5",IF(I3<=15,"4",IF(I3<=30,"3",IF(I3<=45,"2","1"))))
Now this is applied to 10 grids i.e. from I3 to I13.

Now once i have got the desired conditon oriented values, i need to
claculate the average of them. The formula for average is -AVERAGE(I3:I13).
Whenever i do this i get #DIV/0! error.
 
B

Bob Phillips

Try

=AVERAGE(--(I3:I13))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
R

Roger Govier

Hi

If you took the quotes away from the numbers in your IF formula, you will
find that your Average formula works

IF(I3<=0,5,IF(I3<=15,4,IF(I3<=30,3,IF(I3<=45,2,1))))
 
Top