Help with formula whole numbers divided

[panorama]

Hi, first post....

I have a chart that starts at (which has been worked out manually)
$0.01 to $122.14 =1 UNIT
$122.15 to $244.28 =2 UNITS
$244.29 to $366.42 =3 UNITS

and so on this is based on a Unit whose value is $122.14.

I want to create a spreadsheet where the user inputs a dollar amount and the result is displayed for the correct amount of units.

so if I divide the unit $122.14 into say the first row .05 cents I get a result of .0004093.
I want it to be a whole number 1 UNIT if the user inputs .05.

Any idea how to do this???????

Thanks
Stephen

 

[T. Valko]

Hi, first post....

I have a chart that starts at (which has been worked out manually)
$0.01 to $122.14 =1 UNIT
$122.15 to $244.28 =2 UNITS
$244.29 to $366.42 =3 UNITS

and so on this is based on a Unit whose value is $122.14.

I want to create a spreadsheet where the user inputs a dollar amount and the result is displayed for the correct amount of units.

so if I divide the unit $122.14 into say the first row .05 cents I get a result of .0004093.
I want it to be a whole number 1 UNIT if the user inputs .05.

Any idea how to do this???????

Thanks
Stephen

Try this...

A1 = some number like 300.

=CEILING(A1/122.14,1)

 

[indiantrix]

If your 122.14 figure is in cell C1, then this would do the trick...
=IF(MOD(B2,$C$1)>0,INT(B2/$C$1)+1,INT(B2/$C$1))
where B2 is your dollar amount to convert into whole number.
Hope this is what you needed. Of course, you could do the
more efficient function, like T. Valko suggests!...
Larry

 

[panorama]

Thanks for the answers so quickly.......

 

[T. Valko]

Thanks for the answers so quickly.......

You're welcome. Thanks for the feedback!

 

[Soemesh]

Hi Stephen,

Try this formula:

=IF(AND(A1>=0.01,A1<=121.24),"1U",IF(AND(A1>=122.15,A1<=244.28),"2U",IF(AND(A1>=244.29,A1<=366.42),"3U","")))

Best Regards

Soemesh.

 

[panorama]

Hi again, after having a think about this.

I also would like it to produce a max of 100 units as an answer

so if someone entered $12,214.00 which would give the answer 100 units (max allowed).

and then entered a number above this figure instead of answer = 100 units it said "max units allowed"

Hope the above makes sense

 

[T. Valko]

Hi again, after having a think about this.

I also would like it to produce a max of 100 units as an answer

so if someone entered $12,214.00 which would give the answer 100 units (max allowed).

and then entered a number above this figure instead of answer = 100 units it said "max units allowed"

Hope the above makes sense

Try this...

=IF(CEILING(A1/122.14,1)>100,"Max Units Allowed",CEILING(A1/122.14,1))

 

[panorama]

WOW, thanks for the quick response........... works great.

PS Somesh yours works as well but I would have too many cells

 

[Rick Rothstein]

=IF(CEILING(A1/122.14,1)>100,"Max Units Allowed",CEILING(A1/122.14,1))

Why not save Excel the trouble of calculating that first condition...

=IF(A1>12214,"Max Units Allowed",CEILING(A1/122.14,1))