Multiple Average Conditions with #DIV/O! errors. How to get rid of them?

D

DCAbound

New Member
Joined
Dec 3, 2013
Messages
20
Can anyone suggest a fix to my function. Combination average functions and hot to get rid of the #DIV/O! error. This is what I have so far and still does't work.

=IF(AVERAGE(DB9:DD9)>3.25,"Advance",IF(AVERAGE(DB9:DD9)>2.5,"Established",IF(AVERAGE(DB9:DD9)>1.75,"Emerging",IFERROR(AVERAGE(DB9:DD9),"Latent"))))
 
Sort by date Sort by votes
DCAbound said:
3.25>= advance
2.25>= established
1.25>= emerging
all else latent

appreciate your help!!
Click to expand...

Try...
Rich (BB code): 
=IF(AVERAGE(DB9:DD9) < 1.25,"latent",
  LOOKUP(AVERAGE(DB9:DD9),{1.25;2.25;3.25},{"emerging";"established";"advance"}))
 
Last edited:
Upvote 0

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Thanks for your help. Your way is another way to do it but I am still showing the #DIV/O! error message when a cell is left blank.

this is what i used

=IF(AVERAGE(CE27:CI27) <= 1.74,"latent",
LOOKUP(AVERAGE(CE27:CI27),{1.75;2.25;3.25},{"emerging";"established";"advance"}))


[TABLE="width: 569"]
<colgroup><col span="5"><col><col></colgroup><tbody>[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]advance[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]emerging[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]emerging[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]#DIV/0![/TD]
[/TR]
</tbody>[/TABLE]

I am also trying out IFERRORS in the code but it doesn't seem to work.


=IFERROR(AVERAGE(CE27:CI27) <= 1.74,"latent",
LOOKUP(AVERAGE(CE27:CI27),{1.75;2.25;3.25},{"emerging";"established";"advance"}))

Any Ideas?








 
Upvote 0
DCAbound said:
Thanks for your help. Your way is another way to do it but I am still showing the #DIV/O! error message when a cell is left blank.

this is what i used

=IF(AVERAGE(CE27:CI27) <= 1.74,"latent",
LOOKUP(AVERAGE(CE27:CI27),{1.75;2.25;3.25},{"emerging";"established";"advance"}))


[TABLE="width: 569"]
<TBODY>[TR]
[TD="align: right"]4
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]4
[/TD]
[TD][/TD]
[TD]advance
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]3
[/TD]
[TD][/TD]
[TD]emerging
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]3
[/TD]
[TD][/TD]
[TD]emerging
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]#DIV/0!
[/TD]
[/TR]
</TBODY>[/TABLE]

I am also trying out IFERRORS in the code but it doesn't seem to work.


=IFERROR(AVERAGE(CE27:CI27) <= 1.74,"latent",
LOOKUP(AVERAGE(CE27:CI27),{1.75;2.25;3.25},{"emerging";"established";"advance"}))

Any Ideas?
Click to expand...

Try...
Rich (BB code): 
=IFERROR(
  IF(AVERAGE(CE27:CI27) <= 1.74,"latent",
   LOOKUP(AVERAGE(CE27:CI27),{1.75;2.25;3.25},{"emerging";"established";"advance"})),"")

This will trap an error that might occur with the IF/LOOKUP formula.
 
Upvote 0
DCAbound said:
3.25>= advance
2.25>= established
1.25>= emerging
all else latent

appreciate your help!!
Click to expand...

You may try the following

Code: 
[FONT=Lucida Grande]=IFERROR(IF(AVERAGE[COLOR=#006107]([/COLOR][COLOR=#0057d6]DB9:DD9[/COLOR][COLOR=#006107])[/COLOR]>=3.25,"Advance",IF[COLOR=#006107]([/COLOR]AVERAGE[COLOR=#ab30d6]([/COLOR][COLOR=#0057d6]DB9:DD9[/COLOR][COLOR=#ab30d6])[/COLOR]>=2.25,"Established",IF[COLOR=#ab30d6]([/COLOR]AVERAGE[COLOR=#a54a29]([/COLOR][COLOR=#0057d6]DB9:DD9[/COLOR][COLOR=#a54a29])[/COLOR]>=1.25,"Emerging","Latent"[COLOR=#a54a29])[/COLOR][COLOR=#ab30d6])[/COLOR][COLOR=#006107])[/COLOR],"")[/FONT]
 
Upvote 0
May I ask you a few other advise for with adding two sets of data to form a decision rule.

I read somewhere that the + function equates to OR. How would I put two different functions together to form a deacon rule.

this is the code that I have so far.

=IF(AND(CL2=1,CT2=1,CW2=1) + SUM(CM2:CR2)>=5,"Advance", IF(AND(CL2=1,CT2=1) + SUM(CM2:CR2)>=3, "Established", IF(AND(CL2=1,CT2=1) + SUM(CM2:CR2)>=2,"Emerging", IF(AND(CL2=0),"Latent","Latent"))))

I need the + sign to be a condition AND.

[TABLE="width: 260"]
<!--StartFragment--> <colgroup><col width="65" span="4" style="width:65pt"> </colgroup><tbody>[TR]
[TD="width: 65"]Q18[/TD]
[TD="width: 65"]Q19[/TD]
[TD="width: 65"]Q20[/TD]
[TD="width: 65"][/TD]
[/TR]
[TR]
[TD="class: xl71"]a[/TD]
[TD="class: xl71"]>5[/TD]
[TD="class: xl71"]A-D[/TD]
[TD="class: xl71"]Advance[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]>3[/TD]
[TD]a[/TD]
[TD]Established[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]>2[/TD]
[TD]a[/TD]
[TD]Emerging[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD][/TD]
[TD][/TD]
[TD]Latent[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]


 
Upvote 0
I tried this and it works too! Thanks almost had the said formula written but I didn't think of adding ,"") at the end
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Functions IF(AND( functions – multiple conditions I get #VALUE or FALSE Error
Replies
3
Views
787
Johnpow
J
S
  • Locked
  • Question Question
Need help using ERROR.TYPE in an IF statement (not getting antipated result if the condition is false)
Replies
4
Views
696
xenou
xenou
S
  • Question Question
Need help using ERROR.TYPE in an IF statement (not getting antipated result if the condition is false)
Replies
5
Views
2K
Jonmo1
Jonmo1
X
  • Question Question
Help with YTD Average formula.
Replies
6
Views
22K
xcarl86x
X
I
  • Question Question
Complicated formula... can't get rid of DIV/0! Error.
2
Replies
10
Views
1K
Idaho Cowboy
I

Forum statistics

Threads
1,223,909
Messages
6,175,314
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top