Excel Formula Help

[Shahzad Zameer]

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

 

[Sheeloo]

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]

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 ???

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...

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

 

[THendr2929]

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

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 ???

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...

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

 

[Shahzad Zameer]

Thank you dear for your response. But the mentioned formula only put the
round figure in a single column i.e. if I put in A1 = 100 then B4 is 100. If
I put A1 = 200 then B5 is 200 and B4 is 0. If I put A1 = 301 it put in B6 is
300 and B4 and B5 is 0. I don't want like this. Let me clear more:

Suppose I put in A1 = 1001 Then

B4 should be 100
B5 should be 200
B6 should be 700
B7 should be 1

So B4+B5+B6+B7 = 1001

I have to calculate Units cost that has different values i.e. from Unit 1 to
100 it costs $1, from Unit 101 to 300 it costs $2, from Unit 301 to 1000 it
costs $3 and from Unit 1001 it costs $4.

Hope it is more clear now.

Thank you for cooperation,

Best regards,
Shahzad Zameer

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

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 ???

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...

:

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

 

[THendr2929]

I did not pick up on that requirement. My apologies. This can easily be
addressed by creating nested IF statements.
A4: =IF(AND(1<=$A$1,$A$1<=100),$A$1,IF($A$1>100,100,""))
A5: =IF(AND(101<=$A$1,$A$1<=300),$A$1-100,IF($A$1>300,200,""))
A6: =IF(AND(301<=$A$1,$A$1<=1000),$A$1-300,IF($A$1>1000,700,""))
A7: =IF(1000<$A$1,$A$1-1000,"")

Output example below:
635

Units Unit Consumed Cost Unit Charges
1 to 100 100 1 100
101 to 300 200 2 400
301 to 1000 335 3 1005

1000 4

Thank you dear for your response. But the mentioned formula only put the
round figure in a single column i.e. if I put in A1 = 100 then B4 is 100. If
I put A1 = 200 then B5 is 200 and B4 is 0. If I put A1 = 301 it put in B6 is
300 and B4 and B5 is 0. I don't want like this. Let me clear more:

Suppose I put in A1 = 1001 Then

B4 should be 100
B5 should be 200
B6 should be 700
B7 should be 1

So B4+B5+B6+B7 = 1001

I have to calculate Units cost that has different values i.e. from Unit 1 to
100 it costs $1, from Unit 101 to 300 it costs $2, from Unit 301 to 1000 it
costs $3 and from Unit 1001 it costs $4.

Hope it is more clear now.

Thank you for cooperation,

Best regards,
Shahzad Zameer

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

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 ???


:

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...

:

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