Dividing to get a result of whole numbers preset

Zimmerman

Well-known Member
Joined
Jan 22, 2009
Messages
663
Does anybody know the code or the formula to divide a number generated by a formula. Id like to divide this number by 2 different set numbers. Meaning if my number comes out ot 19, id like to divide this by 5.5 and if it's not a whole number which this isn't (it's 3.45), i'd like to round this number to a whole number and add 2.5. My end answer would be 3 5.5 and 1 2.5.

Thanks
Windows XP
Excel 2003
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

taigovinda

Well-known Member
Joined
Mar 28, 2007
Messages
2,639
I'm a little confused as to what you are trying to do.

Does it look like this:

Excel Workbook
ABCD
1163195.5
Sheet1
Excel 2003
Cell Formulas
RangeFormula
C1=A1+B1
D1=ROUND(C1/5.5,0)+2.5

...or maybe it's this (formulas pasted down):
Excel Workbook
ABCD
1163195.5
217.51027.55
Sheet1
Excel 2003
Cell Formulas
RangeFormula
C1=A1+B1
D1=IF(MOD(C1/5.5,1)>0,ROUND(C1/5.5,0)+2.5,C1/5.5)
 

Zimmerman

Well-known Member
Joined
Jan 22, 2009
Messages
663
Not quite. It's more along the lines of this. Sorry I can't insert the pictures like you. But it's along this format

My number I'm starting with is 19.

I want to take this and divide it by 5.5. which that gives me 3.45. With this i am rounding down so this is now just 3.

So im at 5.5 * 3 = 16.5 of the 19 I started with.

anything after the whole number i got 1st (3) I want to make sure is 2.5.
This scenario works out perfect to 19.
So my answer would be I need 3 5.5's and 1 2.5 to make the 19.

If I would start with 20 i'd like to get the answer to say
I need 3 5.5's and 2 2.5's and then my original 20 would now be changed to 21.5.

hopefully this makes sense.
 

taigovinda

Well-known Member
Joined
Mar 28, 2007
Messages
2,639
I'm still not understanding what you are trying to do. If I do to the 20 what you did to the 19, I get this:

Excel Workbook
ABCD
1193.454545316.5
2203.636364422
Sheet1
Excel 2003
Cell Formulas
RangeFormula
B1=A1/5.5
C1=ROUND(B1,0)
D1=C1*5.5



Are you looking for the combination of 5.5s and 2.5s that comes as close as possible to your starting number? Or the ending number has to be *at least* as big as the starting one?
 
Last edited:

taigovinda

Well-known Member
Joined
Mar 28, 2007
Messages
2,639

ADVERTISEMENT

Like this?
Excel Workbook
AB
1old numnew num
21919
32021.5
Sheet1
Excel 2003
Cell Formulas
RangeFormula
B2=LOOKUP(A2+2.499999,INT(A2/5.5)*5.5+(ROW(INDIRECT("1:5"))*2.5))
 

Zimmerman

Well-known Member
Joined
Jan 22, 2009
Messages
663
Yes that is exactly what I'm trying to do. I'm looking for the combination of 5.5 and 2.5. I want to use as many 5.5 as I can and then use a 2.5. And if i have to take the beginning number up some say from 19" to 20" because the combination is telling me to then that's fine. That's what I want.
 

taigovinda

Well-known Member
Joined
Mar 28, 2007
Messages
2,639

ADVERTISEMENT

Is post #5 right?
 

Zimmerman

Well-known Member
Joined
Jan 22, 2009
Messages
663
Yes Post #5 is correct. That's exactly what I was trying to do. Thanks a million. Last problem with this. Do you know how I can now extract that info and C3 say "4" and cell D3 say "1". This is so i can label these columns 5.5 and 2.5.
 

taigovinda

Well-known Member
Joined
Mar 28, 2007
Messages
2,639
Glad I could help :) ...How about this:
Excel Workbook
ABCD
1old num5.5s2.5snew num
2193119
3203221.5
Sheet1
Excel 2003
Cell Formulas
RangeFormula
B2=INT(A2/5.5)
C2=ROUNDUP((A2-B2*5.5)/2.5,0)
D2=LOOKUP(A2+2.499999,INT(A2/5.5)*5.5+(ROW(INDIRECT("1:5"))*2.5))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,837
Messages
5,598,375
Members
414,233
Latest member
WolverineNurse

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top