div/o error

pilot48

New Member
Joined
May 10, 2002
Messages
3
I have a reasonably complicated spreadsheet which includes whatif functions in certain areas. In one particular "if" function, the formula includes a divisible calculation that may under certain circumstances become "0" but in other circumstances become a number greater than "0". In other words the "if" function needs this formula. How do I have excel ignore the "div/0" error when this condition exists. Here is the formula:

=IF(DL23*(AJ23+AK23)/SUM(AJ23:BI23)<DB23,DL23*(AJ23+AK23)/SUM(AJ23:BI23),DB23)

DL23 will always have a number great than "0"
AJ23 & AK23 may not have a number great than "0"
SUM(AJ23:BI23) may not have a number great than "0"
DB23 may not have a number great than "0"

Your help would be greatly appreciated

Garry
This message was edited by pilot48 on 2002-05-11 14:42
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
On 2002-05-11 14:20, pilot48 wrote:
I have a reasonably complicated spreadsheet which includes whatif functions in certain areas. In one particular "if" function, the formula includes a divisible calculation that may under certain circumstances become "0" but in other circumstances become a number greater than "0". In other words the "if" function needs this formula. How do I have excel ignore the "div/0" error when this condition exists. Here is the formula:

=IF(DL23*(AJ23+AK23)/SUM(AJ23:BI23)<DB23,DL23*(AJ23+AK23)/SUM(AJ23:BI23),DB23)

DL23 will always have a number great than "0"
AJ23 & AK23 may not have a number great than "0"
SUM(AJ23:BI23) may not have a number great than "0"
DB23 may not have a number great than "0"

Your help would be greatly appreciated

Garry

Basically:
=if(my DIVISOR = 0, then Show 0 or "",Otherwise show my [FORMULA])
 
Upvote 0
On 2002-05-11 14:20, pilot48 wrote:
I have a reasonably complicated spreadsheet which includes whatif functions in certain areas. In one particular "if" function, the formula includes a divisible calculation that may under certain circumstances become "0" but in other circumstances become a number greater than "0". In other words the "if" function needs this formula. How do I have excel ignore the "div/0" error when this condition exists. Here is the formula:

=IF(DL23*(AJ23+AK23)/SUM(AJ23:BI23)<DB23,DL23*(AJ23+AK23)/SUM(AJ23:BI23),DB23)

DL23 will always have a number great than "0"
AJ23 & AK23 may not have a number great than "0"
SUM(AJ23:BI23) may not have a number great than "0"
DB23 may not have a number great than "0"

Your help would be greatly appreciated

Garry

=MAX(IF(SUM(AJ23:BI23),(DL23*(AJ23+AK23))/SUM(AJ23:BI23),0),DB23)

could be what you are looking for?
 
Upvote 0
Hi Ivan
I'm sorry but I dids not post my entire formula...could you suggest the amendment

=IF(DL23*(AJ23+AK23)/SUM(AJ23:BI23)<DB23,DL23*(AJ23+AK23)/SUM(AJ23:BI23),DB23)

Thanks Garry
This message was edited by pilot48 on 2002-05-11 14:41
 
Upvote 0
Thats strange ?? I answered your post
Assuming what was posted as;

=IF(DL23*(AJ23+AK23)/SUM(AJ23:BI23)
DL23 will always have a number great than "0"
AJ23 & AK23 may not have a number great than "0"
SUM(AJ23:BI23) may not have a number great than "0"
DB23 may not have a number great than "0"

Your help would be greatly appreciated

Garry


And Alladins shows:
=IF(DL23*(AJ23+AK23)/SUM(AJ23:BI23)<DB23,DL23*(AJ23+AK23)/SUM(AJ23:BI23),DB23)

DL23 will always have a number great than "0"
AJ23 & AK23 may not have a number great than "0"
SUM(AJ23:BI23) may not have a number great than "0"
DB23 may not have a number great than "0"

Your help would be greatly appreciated

Garry

I see why now ......

Go with what Alladin has given...
 
Upvote 0
Hi
Aladin's suggestion worked in terms of removing the div/o error, but it does not default to DB23 in the event that there is a numerical value in that cell after the formula tests to the calculation
DL23*(AJ23+AK23)/SUM(AJ23:BI23)<DB23
Any suggestions
Garry
 
Upvote 0
On 2002-05-11 15:23, pilot48 wrote:
Hi
Aladin's suggestion worked in terms of removing the div/o error, but it does not default to DB23 in the event that there is a numerical value in that cell after the formula tests to the calculation
DL23*(AJ23+AK23)/SUM(AJ23:BI23)<DB23
Any suggestions
Garry

Garry,

You lost me: The formula I suggested doesn't have the test:

DL23*(AJ23+AK23)/SUM(AJ23:BI23)<DB23

It's rather meant to replace your original formula as a whole. The formula

=MAX(IF(SUM(AJ23:BI23),(DL23*(AJ23+AK23))/SUM(AJ23:BI23),0),DB23)

compares

the result of

(DL23*(AJ23+AK23))/SUM(AJ23:BI23)

with the value in DB23, picks out whichever is bigger.

Now it is up to you to say what must be the case, so we can adjust the formula.

Aladin
This message was edited by Aladin Akyurek on 2002-05-11 15:34
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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