Thanks:  0
Likes:  0

1. 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)
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 ]

2. 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)
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 [FORMULA] DIVISOR = 0, then Show 0 or "",Otherwise show my [FORMULA])

3. 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)
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?

4. 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)
Thanks Garry

[ This Message was edited by: pilot48 on 2002-05-11 14:41 ]

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

=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
I see why now ......

Go with what Alladin has given...

6. 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) Any suggestions
Garry

7. 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) Any suggestions
Garry
Garry,

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

DL23*(AJ23+AK23)/SUM(AJ23:BI23)
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.

[ This Message was edited by: Aladin Akyurek on 2002-05-11 15:34 ]

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•