|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#1
July 18th, 2007, 04:38 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Function IF AND
Hi Not too good at maths, trying to produce a formula to calulate discounts.
if a0.25 and b10 discount =Z if a0.25 and b49 discount =Y if a1 and b10 discount =X if a1 and b49 discount =W if b499 Discount =V I Have "=IF(AND(C40.25,B510),I15,0)" for 1st line but can't seem to add other lines to the formula is it possible to have 1 formula that carries out all the calulations? 
|
|
#2
July 18th, 2007, 04:54 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Function IF AND
If you wanted to go the route of a nested IF statement, then this may work
for you. =IF(C40.25,IF(B510,I15,IF(B549,"DiscountYCell", 0)),IF(C41,IF(B510,"DiscountXCell",IF(B449,"Dis countWCell",0)),IF(C4499,"DiscountVCell",0))) However, you may want to consider creating a table and using VLOOKUP. HTH, Paul "Indigo" wrote in message ... Hi Not too good at maths, trying to produce a formula to calulate discounts. if a0.25 and b10 discount =Z if a0.25 and b49 discount =Y if a1 and b10 discount =X if a1 and b49 discount =W if b499 Discount =V I Have "=IF(AND(C40.25,B510),I15,0)" for 1st line but can't seem to add other lines to the formula is it possible to have 1 formula that carries out all the calulations?
|
|
#3
July 18th, 2007, 04:55 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Function IF AND
=IF(AND(A40.25,B410),Z,IF(AND(A40.25,B449),Y,I F(AND(A41,B410),X,IF(AND(A41,B449),W,IF(B4499 ,V,"Invalid")))))
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Indigo" wrote in message ... Hi Not too good at maths, trying to produce a formula to calulate discounts. if a0.25 and b10 discount =Z if a0.25 and b49 discount =Y if a1 and b10 discount =X if a1 and b49 discount =W if b499 Discount =V I Have "=IF(AND(C40.25,B510),I15,0)" for 1st line but can't seem to add other lines to the formula is it possible to have 1 formula that carries out all the calulations?
|
|
#4
July 18th, 2007, 05:32 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Function IF AND
Thanks for getting back so quickly, I've filled the formula as below to test
and it's not working, any suggestion please. =IF(AND(C40.25,B510),5,IF(AND(C40.25,B549),7.5 ,IF(AND(C41,B510),10,IF(AND(C41,B549),15,IF(B5 499,20,0))))) "Bob Phillips" wrote: =IF(AND(A40.25,B410),Z,IF(AND(A40.25,B449),Y,I F(AND(A41,B410),X,IF(AND(A41,B449),W,IF(B4499 ,V,"Invalid"))))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Indigo" wrote in message ... Hi Not too good at maths, trying to produce a formula to calulate discounts. if a0.25 and b10 discount =Z if a0.25 and b49 discount =Y if a1 and b10 discount =X if a1 and b49 discount =W if b499 Discount =V I Have "=IF(AND(C40.25,B510),I15,0)" for 1st line but can't seem to add other lines to the formula is it possible to have 1 formula that carries out all the calulations?
|
|
#5
July 18th, 2007, 05:36 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Function IF AND
Hi Filled in details to test formula as below and it's not working any
suggestion please? =IF(C40.25,IF(B510,5,IF(B549,7.5,0)),IF(C41,IF (B510,10,IF(B449,15,0)),IF(C4499,20,0))) not sure what a VLOOKUP is will search help Phil "PCLIVE" wrote: If you wanted to go the route of a nested IF statement, then this may work for you. =IF(C40.25,IF(B510,I15,IF(B549,"DiscountYCell", 0)),IF(C41,IF(B510,"DiscountXCell",IF(B449,"Dis countWCell",0)),IF(C4499,"DiscountVCell",0))) However, you may want to consider creating a table and using VLOOKUP. HTH, Paul "Indigo" wrote in message ... Hi Not too good at maths, trying to produce a formula to calulate discounts. if a0.25 and b10 discount =Z if a0.25 and b49 discount =Y if a1 and b10 discount =X if a1 and b49 discount =W if b499 Discount =V I Have "=IF(AND(C40.25,B510),I15,0)" for 1st line but can't seem to add other lines to the formula is it possible to have 1 formula that carries out all the calulations?
|
|
#6
July 18th, 2007, 05:40 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Function IF AND
How is it not working? What is it doing or not doing?
Gives us an example of your data in C4 and B5 and what result you expect. Regards, Paul "Indigo" wrote in message ... Hi Filled in details to test formula as below and it's not working any suggestion please? =IF(C40.25,IF(B510,5,IF(B549,7.5,0)),IF(C41,IF (B510,10,IF(B449,15,0)),IF(C4499,20,0))) not sure what a VLOOKUP is will search help Phil "PCLIVE" wrote: If you wanted to go the route of a nested IF statement, then this may work for you. =IF(C40.25,IF(B510,I15,IF(B549,"DiscountYCell", 0)),IF(C41,IF(B510,"DiscountXCell",IF(B449,"Dis countWCell",0)),IF(C4499,"DiscountVCell",0))) However, you may want to consider creating a table and using VLOOKUP. HTH, Paul "Indigo" wrote in message ... Hi Not too good at maths, trying to produce a formula to calulate discounts. if a0.25 and b10 discount =Z if a0.25 and b49 discount =Y if a1 and b10 discount =X if a1 and b49 discount =W if b499 Discount =V I Have "=IF(AND(C40.25,B510),I15,0)" for 1st line but can't seem to add other lines to the formula is it possible to have 1 formula that carries out all the calulations?
|
|
#7
July 18th, 2007, 06:00 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Function IF AND
This is what I'd expect & in () what's happening,
C4 = 0.25 & B5 = 10 answer = 0 (this works) C4 = 0.26 & B5 = 11 answer = 5 (this works) C4 = 0.26 & B5 = 50 answer = 7.5 (however I change data never goes above 5) C4 = 1.1 & B5 = 11 answer = 10 C4 = 1.1 & B5 = 50 answer = 15 B5 = 500 answer = 20 Phil "PCLIVE" wrote: How is it not working? What is it doing or not doing? Gives us an example of your data in C4 and B5 and what result you expect. Regards, Paul "Indigo" wrote in message ... Hi Filled in details to test formula as below and it's not working any suggestion please? =IF(C40.25,IF(B510,5,IF(B549,7.5,0)),IF(C41,IF (B510,10,IF(B449,15,0)),IF(C4499,20,0))) not sure what a VLOOKUP is will search help Phil "PCLIVE" wrote: If you wanted to go the route of a nested IF statement, then this may work for you. =IF(C40.25,IF(B510,I15,IF(B549,"DiscountYCell", 0)),IF(C41,IF(B510,"DiscountXCell",IF(B449,"Dis countWCell",0)),IF(C4499,"DiscountVCell",0))) However, you may want to consider creating a table and using VLOOKUP. HTH, Paul "Indigo" wrote in message ... Hi Not too good at maths, trying to produce a formula to calulate discounts. if a0.25 and b10 discount =Z if a0.25 and b49 discount =Y if a1 and b10 discount =X if a1 and b49 discount =W if b499 Discount =V I Have "=IF(AND(C40.25,B510),I15,0)" for 1st line but can't seem to add other lines to the formula is it possible to have 1 formula that carries out all the calulations?
|
|
#8
July 18th, 2007, 06:03 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Function IF AND
That is not a good reply, how can anyone help you when all you say is that
it's not working. Surely you can't expect people to be mind readers? What did not work, did you get an error, was the result unexpected or did your computer blow up? -- Regards, Peo Sjoblom "Indigo" wrote in message ... Thanks for getting back so quickly, I've filled the formula as below to test and it's not working, any suggestion please. =IF(AND(C40.25,B510),5,IF(AND(C40.25,B549),7.5 ,IF(AND(C41,B510),10,IF(AND(C41,B549),15,IF(B5 499,20,0))))) "Bob Phillips" wrote: =IF(AND(A40.25,B410),Z,IF(AND(A40.25,B449),Y,I F(AND(A41,B410),X,IF(AND(A41,B449),W,IF(B4499 ,V,"Invalid"))))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Indigo" wrote in message ... Hi Not too good at maths, trying to produce a formula to calulate discounts. if a0.25 and b10 discount =Z if a0.25 and b49 discount =Y if a1 and b10 discount =X if a1 and b49 discount =W if b499 Discount =V I Have "=IF(AND(C40.25,B510),I15,0)" for 1st line but can't seem to add other lines to the formula is it possible to have 1 formula that carries out all the calulations?
|
|
#9
July 18th, 2007, 06:49 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Function IF AND
Try this:
=IF(B5499,20,IF(C41,IF(B549,15,IF(B510,10,0)), IF(C40.25,IF(B549,7.5,IF(B510,5,0)),0))) HTH, Paul "Indigo" wrote in message ... This is what I'd expect & in () what's happening, C4 = 0.25 & B5 = 10 answer = 0 (this works) C4 = 0.26 & B5 = 11 answer = 5 (this works) C4 = 0.26 & B5 = 50 answer = 7.5 (however I change data never goes above 5) C4 = 1.1 & B5 = 11 answer = 10 C4 = 1.1 & B5 = 50 answer = 15 B5 = 500 answer = 20 Phil "PCLIVE" wrote: How is it not working? What is it doing or not doing? Gives us an example of your data in C4 and B5 and what result you expect. Regards, Paul "Indigo" wrote in message ... Hi Filled in details to test formula as below and it's not working any suggestion please? =IF(C40.25,IF(B510,5,IF(B549,7.5,0)),IF(C41,IF (B510,10,IF(B449,15,0)),IF(C4499,20,0))) not sure what a VLOOKUP is will search help Phil "PCLIVE" wrote: If you wanted to go the route of a nested IF statement, then this may work for you. =IF(C40.25,IF(B510,I15,IF(B549,"DiscountYCell", 0)),IF(C41,IF(B510,"DiscountXCell",IF(B449,"Dis countWCell",0)),IF(C4499,"DiscountVCell",0))) However, you may want to consider creating a table and using VLOOKUP. HTH, Paul "Indigo" wrote in message ... Hi Not too good at maths, trying to produce a formula to calulate discounts. if a0.25 and b10 discount =Z if a0.25 and b49 discount =Y if a1 and b10 discount =X if a1 and b49 discount =W if b499 Discount =V I Have "=IF(AND(C40.25,B510),I15,0)" for 1st line but can't seem to add other lines to the formula is it possible to have 1 formula that carries out all the calulations?
|
|
#10
July 18th, 2007, 06:50 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel Function IF AND
Hi Peo
Sorry about the bad post, 1st time I've posted anything, should have thought it through before sending. details see previous reply to PCLICVE, when I change my 2 variables, for the 1st 2 quantities the correct answer is return after that increasing the quantites does not increase the discount. It seems that this part of the formula is being acted on i.e."=IF(AND(C40.25,B510),I15,0)" then the calculation stops. Thanks all for the time you've spent on this, Phil "Peo Sjoblom" wrote: That is not a good reply, how can anyone help you when all you say is that it's not working. Surely you can't expect people to be mind readers? What did not work, did you get an error, was the result unexpected or did your computer blow up? -- Regards, Peo Sjoblom "Indigo" wrote in message ... Thanks for getting back so quickly, I've filled the formula as below to test and it's not working, any suggestion please. =IF(AND(C40.25,B510),5,IF(AND(C40.25,B549),7.5 ,IF(AND(C41,B510),10,IF(AND(C41,B549),15,IF(B5 499,20,0))))) "Bob Phillips" wrote: =IF(AND(A40.25,B410),Z,IF(AND(A40.25,B449),Y,I F(AND(A41,B410),X,IF(AND(A41,B449),W,IF(B4499 ,V,"Invalid"))))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Indigo" wrote in message ... Hi Not too good at maths, trying to produce a formula to calulate discounts. if a0.25 and b10 discount =Z if a0.25 and b49 discount =Y if a1 and b10 discount =X if a1 and b49 discount =W if b499 Discount =V I Have "=IF(AND(C40.25,B510),I15,0)" for 1st line but can't seem to add other lines to the formula is it possible to have 1 formula that carries out all the calulations?
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
