Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formula Help
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formula Help
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formula Help
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formula Help
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formula Help
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 mo 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 "THendr2929" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Formula Help
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$1100,100,"" )) A5: =IF(AND(101<=$A$1,$A$1<=300),$A$1-100,IF($A$1300,200,"")) A6: =IF(AND(301<=$A$1,$A$1<=1000),$A$1-300,IF($A$11000,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 "Shahzad Zameer" wrote: 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 mo 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 "THendr2929" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
Excel Formula Doesn't Execute (Shows formula-not the calcuation) | Excel Discussion (Misc queries) | |||
match formula - 2 excel files:#1 hasthis formula, 2nd has the Raw | Excel Worksheet Functions | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions |