Trouble with IF statement :: MrExcel Message Board
 Search   Memberlist   Usergroups   Favorites   Statistics   Register

 Trouble with IF statement

garender
Welcome to the Board

Joined: 18 Aug 2003
Posts: 2

Flag:

Status: Offline

Trouble with IF statement

I'm trying not to value a cell when either 1 of 2 cells =0. For example, in trying to determine a gross collection % for the following cell values: Col D:Payments =0.0, Col F:adjustments=2000.00, and Col H: charges=5000.00. My formula =IF ((d5=0),"",(d5/h5)) displays a blank which is o.k., but when Col D: Payments =1000.00, Col F: adjustments=2000.00 and Col H: charges = 0.0 the formular returns a #DIV/0!.

I wanted to consider either cell D or cell H containing a zero and returning a blank. Thanks.

Fri Sep 19, 2003 7:13 pm

kskinne
Board Master

Joined: 18 Feb 2002
Posts: 948

Flag:

Status: Offline

Re: Trouble with IF statement

try this:

=IF(OR(D5=0,H5=0),"",(D5/H5))

hth
kevin

Fri Sep 19, 2003 7:20 pm

just_jon
MrExcel MVP

Joined: 04 Sep 2002
Posts: 4007
Location: Huntsville AL
Flag:

Status: Offline

Re: Trouble with IF statement

Something like this, perhaps --

 Microsoft Excel - Price Comparisons.xls ___Running: xl2000 : OS = Windows ME
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 I1I2I3I4I5 =

D
E
F
G
H
I
1
10   52
2
22   112
3
99   0
4
0   99
5

 Sheet2

[HtmlMaker 2.40] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

_________________
just_jon
Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

Fri Sep 19, 2003 7:23 pm

Mat
Board Master

Joined: 17 Sep 2003
Posts: 451
Location: Montréal, Québec
Flag:

Status: Offline

Re: Trouble with IF statement

You should use something like this :

=IF(OR(D5=0;H5=0);"";D5/H5)

Fri Sep 19, 2003 7:25 pm

Mark W.
MrExcel MVP

Joined: 11 Feb 2002
Posts: 7146
Location: Austin, Texas USA
Flag:

Status: Offline

Re: Trouble with IF statement

You can avoid #DIV/0! simply by evaluating the denominator...

=IF(H5,D5/H5,"")

Fri Sep 19, 2003 7:38 pm
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First

Forum Jump:
 Jump to: Select a forum MrExcel Forums----------------Excel QuestionsInternational forumMicrosoft AccessTechnical issues and Future development Holy Macro! Books----------------Holy Macro! Products The Lounge----------------Max Cells Lounge Announcements----------------About This BoardHall of Fame WinnersTest Here

Page 1 of 1

Forum Rules:
 You cannot post new topics in this forumYou cannot reply to topics in this forumYou cannot edit your posts in this forumYou cannot delete your posts in this forumYou cannot vote in polls in this forum