Suppose if I put in A1 = 100

Then the value in B4 = 100 and the rest in 0 i.e. B5, B6 and B7 should be 0.

And if I put A1 = 101

Then the value in B4 is 100 and B5 is 1 and rest should be 0.

And so on.

Is it clear ???


"Sheeloo" wrote:

Not clear...
Give information in this format
A1=100 to show that A1 has 100
[B1]=200 [how to calculate] to show that B1 is to have a formula resulting
in 200 and showing the logic...

Do this till logic is clear...

"Shahzad Zameer" wrote:

Hi Experts,

I am using MSOffice 2003 and I need help for a formula. I need a formula for
the following:

If I put a figure in A1 i.e. 301

And I want the values in the following format i.e.

Units. Unit Consumed. Cost.
Unit Charges
--------------- ---------------- -------
--------------
A4 = 1 to 100 B4 = 100.00 C4 = 1
B4 * C4
A5 = 101 to 300 B5 = 200.00 C5 = 2
B5 * C5
A6 = 301 to 1000 B6 = 1.00 C6 = 3
B6 * C6
A7 = 1000 B7 = - C7 = 4
B7 * C7

Hope you understand the above mentioned requirement. I want in B4 column the
value in 100 if less than or equal to 100.
In B5 if the value is greater than 100 or equal to 300. But the sum of B4
and B5 will be 300 and remaining value will be in B6
In B6 if the value is greater than 300 or equal to 1000 and so on.

Please help me to find out the formula. If I made mistake to describe my
point of view I apologize for it.

Thanks and regards,
Shahzad Zameer