ROUNDDOWN(IF( ...)) CALCULATION
 

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 its found, as its 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

ROUNDDOWN(IF( ...)) CALCULATION
 

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.

--
Biff
Microsoft Excel MVP


"PoetsOnMars" wrote in message
...
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

ROUNDDOWN(IF( ...)) CALCULATION
 

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)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"PoetsOnMars" wrote:

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 its found, as its 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

ROUNDDOWN(IF( ...)) CALCULATION
 

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! :)

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"PoetsOnMars" wrote in message
...
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

ROUNDDOWN(IF( ...)) CALCULATION
 

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

"Luke M" wrote:

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)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"PoetsOnMars" wrote:

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 its found, as its 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

ROUNDDOWN(IF( ...)) CALCULATION
 

See if this does what you want:

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

Format as Percentage 0 decimal places.

--
Biff
Microsoft Excel MVP


"PoetsOnMars" wrote in message
...
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! :)

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"PoetsOnMars" wrote in message
...
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

ROUNDDOWN(IF( ...)) CALCULATION
 

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.

--
Biff
Microsoft Excel MVP


"PoetsOnMars" wrote in message
...
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) ....

"Luke M" wrote:

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)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"PoetsOnMars" wrote:

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