The easiest way is to set up a different "IF" formula for each of the cells
B4,B5,B6 and B7 based upon your requirement in column A.

Use these formulae:
B4: =IF(AND(1<=$A$1,$A$1<=100),$A$1,"")
B5: =IF(AND(101<=$A$1,$A$1<=300),$A$1,"")
B6: =IF(AND(301<=$A$1,$A$1<=1000),$A$1,"")
B7: =IF(1000<$A$1,$A$1,"")

Copy these formula into your cells, and use Excel Help to understand the
parts of the IF and AND functions. The FALSE result of the IF function is
set to "" (blank), otherwise it displays "FALSE" in the cell. You can change
this to display whatever you prefer.

Similar formulae can be entered in cells D4,D5,D6 and D7 so the result cells
remain blank if you prefer. Copy the following formulae:
D4: =IF(B4="","",B4*C4)
D5: =IF(B5="","",B5*C5)
D6: =IF(B6="","",B6*C6)
D7: =IF(B7="","",B7*C7)
(I set these cells to display "" (blank) if the corresponding Column B cell
is blank.)

The following was copied from Excel to show you how it looks once the
formulae are entered:

301

Units Unit Consumed Cost Unit Charges
1 to 100 1
101 to 300 2
301 to 1000 301 3 903
1000 4


"Shahzad Zameer" wrote:

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