Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help: Modulo... not... quite...right...
Hey all good helping people.
I am trying to create a Modulo function to fill out a round/pick calculation in a draft type spreadsheet. Specifically, I want the value: 1 to show as 1:1 and the value: 12 to show as 1:12. The formula that I have now is: =ROUNDDOWN((P3/12)+1,0)&"/"&MOD(P3,12) (where the number is in P3) The formula works fine, until I get to the value of 12, then it calculates to 2:0 and not 1:12 (which is my goal). Do I need to add some if/then logic for when the modulo =0, then subtract one from the 1st part, and set the second part =12? Or is there a better way? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help: Modulo... not... quite...right...
On Sep 6, 2:43*pm, Commish wrote:
Hey all good helping people. I am trying to create a Modulo function to fill out a round/pick calculation in a draft type spreadsheet. Specifically, I want the value: 1 to show as 1:1 and the value: 12 to show as 1:12. The formula that I have now is: =ROUNDDOWN((P3/12)+1,0)&"/"&MOD(P3,12) (where the number is in P3) The formula works fine, until I get to the value of 12, then it calculates to 2:0 and not 1:12 (which is my goal). Do I need to add some if/then logic for when the modulo =0, then subtract one from the 1st part, and set the second part =12? Or is there a better way? Nevermind, I'm going with this: =ROUNDUP(P3/12,0)&":"&IF(MOD(P3,12)=0,12,MOD(P3,12)) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help: Modulo... not... quite...right...
Commish explained on 9/6/2011 :
Hey all good helping people. I am trying to create a Modulo function to fill out a round/pick calculation in a draft type spreadsheet. Specifically, I want the value: 1 to show as 1:1 and the value: 12 to show as 1:12. The formula that I have now is: =ROUNDDOWN((P3/12)+1,0)&"/"&MOD(P3,12) (where the number is in P3) The formula works fine, until I get to the value of 12, then it calculates to 2:0 and not 1:12 (which is my goal). Do I need to add some if/then logic for when the modulo =0, then subtract one from the 1st part, and set the second part =12? Or is there a better way? You can format the target cell with the following Custom format... "1:"# ...and just use =IF(MOD(P3,12)=0,12,MOD(P3,12)) so that the cell contains a usable value which you can use in calculations and/or in other formulas. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help: Modulo... not... quite...right...
GS explained on 9/6/2011 :
Commish explained on 9/6/2011 : Hey all good helping people. I am trying to create a Modulo function to fill out a round/pick calculation in a draft type spreadsheet. Specifically, I want the value: 1 to show as 1:1 and the value: 12 to show as 1:12. The formula that I have now is: =ROUNDDOWN((P3/12)+1,0)&"/"&MOD(P3,12) (where the number is in P3) The formula works fine, until I get to the value of 12, then it calculates to 2:0 and not 1:12 (which is my goal). Do I need to add some if/then logic for when the modulo =0, then subtract one from the 1st part, and set the second part =12? Or is there a better way? You can format the target cell with the following Custom format... "1:"# ..and just use =IF(MOD(P3,12)=0,12,MOD(P3,12)) so that the cell contains a usable value which you can use in calculations and/or in other formulas. With my suggestion, note that... If P3=24; Target= 1:12 Using... =ROUNDUP(P3/12,0)&":"&IF(MOD(P3,12)=0,12,MOD(P3,12)) Target= "2:12" ...which Excel will treat as Time if you try to use it in formulas. A better way to go might be to use 2 cols so you can use both parts of your formula in each col: Example: A3: =ROUNDUP(P3/12,0) B3: =IF(MOD(P3,12)=0,12,MOD(P3,12)); Left-Aligned ..where either col can use a custom format to display the colon. This, then, will now allow you to use the cell values in calculations and other formulas. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|