rounding sums base on value
 

I struggle with trying to add multiple if statements with other formulas such
as ceiling, round and mrounds. So i am asking for help with the following.

I am multiplying column A by Column B. The totals range from 48 to 34876.
There are 20,000 rows. I would like the values rounded in the following
manner:

50 to 500 to the next 50 i.e 165 would go to 200
501 to 2500 to the next 100
2501 to 4000 to the next 250
4001 to 20000 to the next 500
20001 + to the 1000
--

thanks in advance
inthestands

rounding sums base on value
 

=IF(A1<=500,CEILING(A1,50),IF(A1<=2500,CEILING(A1, 100),IF(A1<=4000,CEILING(A1,250),IF(A1<=20000,CEIL ING(A1,500),CEILING(A1,1000)))))
--
David Biddulph

"inthestands" wrote in message
...
I struggle with trying to add multiple if statements with other formulas
such
as ceiling, round and mrounds. So i am asking for help with the
following.

I am multiplying column A by Column B. The totals range from 48 to 34876.
There are 20,000 rows. I would like the values rounded in the following
manner:

50 to 500 to the next 50 i.e 165 would go to 200
501 to 2500 to the next 100
2501 to 4000 to the next 250
4001 to 20000 to the next 500
20001 + to the 1000
--

thanks in advance
inthestands

rounding sums base on value
 

Just another way:

=CEILING(A1,LOOKUP(A1,{0,500,2500,4000,20000},{50, 100,250,500,1000}))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"inthestands" wrote in message
...
I struggle with trying to add multiple if statements with other formulas
such
as ceiling, round and mrounds. So i am asking for help with the
following.

I am multiplying column A by Column B. The totals range from 48 to 34876.
There are 20,000 rows. I would like the values rounded in the following
manner:

50 to 500 to the next 50 i.e 165 would go to 200
501 to 2500 to the next 100
2501 to 4000 to the next 250
4001 to 20000 to the next 500
20001 + to the 1000
--

thanks in advance
inthestands