ROUNDDOWN(IF( ...)) CALCULATION

[PoetsOnMars]

Original Post:

Have IF function that I need to rounddown the results for, which are in
percentage format. The function is in B7 and says: IF cell F7 is empty,"no
data",F7/F8'. Want the dividend that results in B7 to rounddown to 0 decimal
places, but can't seem to combine the =rounddown(if ...) and come up w/
anything but a ZERO value in the destination cell – B7 - no matter how the
rounddown/if statements are arranged.

Received the following solutions from original post:

=ROUNDDOWN(IF(ISBLANK(F7),"no data",F7/F8),0)

=IF(F7="","no data",ROUNDDOWN(F7/F8,0))

NOTE: Neither of these solutions worked. In every case, no matter how they
were arranged or rearranged, they resulted in a ZERO value in the cell,
regardless of what F7/F8 was. Has to be a syntax error? But maybe not. Read
on:

Perhaps I neglected to explain in enough detail 1st time around:

The F7 cell reference in the IF formula is dependent on another cell. The IF
function reads:
IF(F7=0,"NO DATA",F7/F8)

In F7 the following formula is present:
=F242, where F242 is a SUM from a series of other values tallied in that
cell. So F7 is in fact the value =F242. In turn, the value of F8 is actually
=E242, the SUM of another series of values. B7 is then the resulting
percentage of f242/e242, but relocated by cell reference to the top of the
sheet in which it’s found, as it’s part of a summary of the calculations on
the sheet. The result in B7 gives the percentage of that result as a quick
reference that is used in other calculations on other sheets.

NOW can you help? )) THX / POM

 

[T. Valko]

If F7 is less than F8 then F7/F8 will be <1 and ROUNDDOWN(...,0) rounds down
to the nearest integer which is 0. So, you might want it to do something
other than round down.

How about showing us some examples of what F7 and F8 are and what results
you expect.

 

[Luke M]

If you are wanting a percentage, 2 ideas:
change formula to:
=ROUNDDOWN(IF(ISBLANK(F7),"no data",100*F7/F8),0)

Or, if you want to format cell to percentage, then change formula to
=ROUNDDOWN(IF(ISBLANK(F7),"no data",F7/F8),2)

 

[PoetsOnMars]

You're correct: the result of F7/F8 is typically < 1, with figures like
278/310 giving a result of .896774. Displayed as a percentage it's 89.68, but
I want it read "89" w/ no decimals. Excel automatically rounds up, which
creates some other havoc, so need it to rounddown. Solution? Tried doing
F7*100/F8, but that results in 8900% - which clearly doesn't help either ....
So how do you work around this?

Thanks! We're close --- I can FEEL it!

 

[PoetsOnMars]

YOU-REEKA! It works: Either incarnation:

=ROUNDDOWN(IF(F397="Date Required","NO DATA",F397/F398),2)

OR

=ROUNDDOWN(IF(ISBLANK(F397),"NO DATA",F397/F398),2)

I'm not sure, but think I just learned something about the quirks of Excel
and converting decimal fractions into percentages. (Now if I only knew WHAT I
learned) ....

 

[T. Valko]

See if this does what you want:

=IF(F7="","No Data",IF(F8="",0,ROUND(F7/F8,2)))

Format as Percentage 0 decimal places.

 

[T. Valko]

I don't think you want to use either of those formulas.

If F397 = "Date Required" the formula will return an error.

If F398 is empty the formula will return an error.

See my other reply.