| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Applying formula on formula
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Applying formula on formula
Hi
Show some examples of the If formulae you are using, and the formula you are using to calculate the average. -- Regards Roger Govier "Aditya Kumar" <Aditya wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Applying formula on formula
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. "Roger Govier" wrote: Hi Show some examples of the If formulae you are using, and the formula you are using to calculate the average. -- Regards Roger Govier "Aditya Kumar" <Aditya wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Applying formula on formula
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) "Aditya Kumar" wrote in message ... 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. "Roger Govier" wrote: Hi Show some examples of the If formulae you are using, and the formula you are using to calculate the average. -- Regards Roger Govier "Aditya Kumar" <Aditya wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Applying formula on formula
Thanks dude...it worked...from where i can learn more on this array...
"Bob Phillips" wrote: 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) "Aditya Kumar" wrote in message ... 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. "Roger Govier" wrote: Hi Show some examples of the If formulae you are using, and the formula you are using to calculate the average. -- Regards Roger Govier "Aditya Kumar" <Aditya wrote in message ... 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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Applying formula on formula
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)) )) -- Regards Roger Govier "Aditya Kumar" wrote in message ... 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. "Roger Govier" wrote: Hi Show some examples of the If formulae you are using, and the formula you are using to calculate the average. -- Regards Roger Govier "Aditya Kumar" <Aditya wrote in message ... 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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Applying formula on formula
You're storing the IF results as text. Get rid of the quotation marks
around them. On Oct 31, 4:40 am, Aditya Kumar wrote: 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. "Roger Govier" wrote: Hi Show some examples of the If formulae you are using, and the formula you are using to calculate the average. -- Regards Roger Govier "Aditya Kumar" <Aditya wrote in message ... 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.- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Applying a cell format within a Formula | Excel Discussion (Misc queries) | |||
| Need help applying a formula to a certain area of the worksheet | Excel Worksheet Functions | |||
| applying formula | Excel Discussion (Misc queries) | |||
| Applying a formula to an entire column | Excel Discussion (Misc queries) | |||
| Applying colour to my Formula | Excel Discussion (Misc queries) | |||
Linear Mode
