Dividing to get a result of whole numbers preset

[Zimmerman]

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

 

[taigovinda]

I'm a little confused as to what you are trying to do.

Does it look like this:

Excel Workbook
	A	B	C	D
1	16	3	19	5.5
Sheet1

Excel 2003

Cell Formulas
Range	Formula
C1	=A1+B1
D1	=ROUND(C1/5.5,0)+2.5

...or maybe it's this (formulas pasted down):

Excel Workbook
	A	B	C	D
1	16	3	19	5.5
2	17.5	10	27.5	5
Sheet1

Excel 2003

Cell Formulas
Range	Formula
C1	=A1+B1
D1	=IF(MOD(C1/5.5,1)>0,ROUND(C1/5.5,0)+2.5,C1/5.5)

 

[Zimmerman]

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]

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
	A	B	C	D
1	19	3.454545	3	16.5
2	20	3.636364	4	22
Sheet1

Excel 2003

Cell Formulas
Range	Formula
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?

 

[taigovinda]

Like this?

Excel Workbook
	A	B
1	old num	new num
2	19	19
3	20	21.5
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B2	=LOOKUP(A2+2.499999,INT(A2/5.5)*5.5+(ROW(INDIRECT("1:5"))*2.5))

 

[Zimmerman]

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]

Is post #5 right?

 

[Zimmerman]

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]

Glad I could help ...How about this:

Excel Workbook
	A	B	C	D
1	old num	5.5s	2.5s	new num
2	19	3	1	19
3	20	3	2	21.5
Sheet1

Excel 2003

Cell Formulas
Range	Formula
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))

 

[Zimmerman]

That works thanks again.