Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rounddown + if function | Excel Worksheet Functions | |||
Excel 2007 - RoundDown in Calculation? | Excel Discussion (Misc queries) | |||
Using ROUNDDOWN - Help please | Excel Discussion (Misc queries) | |||
rounddown | Excel Worksheet Functions | |||
Rounddown problem... | Excel Discussion (Misc queries) |