Logical Function with Vlookup

Topic

New Reply

I am looking for a logical formula to include with a lookup function to do the following

If the value in cell D18 is less than zero then cell D11 must be zero. If the value in cellD18 is greater than zero than cell D18 must be multiplied by 10%. However if cell D14 & D15 has a value and B18 is less than zero then the following table must be used below using a vlookup, using the cumulative value in col C i.e the 3rd column for F/end GP and 6 th col for B/end GP

However if B18 is greater than zero, then B18 must be multiplied by 10% plus following table must be used below, using the cumulative value in col C i.e the 3rd column

Your assistance will be most appreciated

Reply | Quote

Replies

I am looking for a logical formula to include with a lookup function to do the following

If the value in cell D18 is less than zero then cell D11 must be zero. If the value in cellD18 is greater than zero than cell D18 must be multiplied by 10%. However if cell D14 & D15 has a value and B18 is less than zero then the following table must be used below using a vlookup, using the cumulative value in col C i.e the 3rd column for F/end GP and 6 th col for B/end GP

However if B18 is greater than zero, then B18 must be multiplied by 10% plus following table must be used below, using the cumulative value in col C i.e the 3rd column

Your assistance will be most appreciated

Reply | Quote

I am looking for a logical formula to include with a lookup function to do the following

If the value in cell D18 is less than zero then cell D11 must be zero. If the value in cellD18 is greater than zero than cell D18 must be multiplied by 10%. However if cell D14 & D15 has a value and B18 is less than zero then the following table must be used below using a vlookup, using the cumulative value in col C i.e the 3rd column for F/end GP and 6 th col for B/end GP

However if B18 is greater than zero, then B18 must be multiplied by 10% plus following table must be used below, using the cumulative value in col C i.e the 3rd column

Your assistance will be most appreciated

Reply | Quote

I am looking for a logical formula to include with a lookup function to do the following

If the value in cell D18 is less than zero then cell D11 must be zero. If the value in cellD18 is greater than zero than cell D18 must be multiplied by 10%. However if cell D14 & D15 has a value and B18 is less than zero then the following table must be used below using a vlookup, using the cumulative value in col C i.e the 3rd column for F/end GP and 6 th col for B/end GP

However if B18 is greater than zero, then B18 must be multiplied by 10% plus following table must be used below, using the cumulative value in col C i.e the 3rd column

Your assistance will be most appreciated

Reply | Quote

I am looking for a logical formula to include with a lookup function to do the following

If the value in cell D18 is less than zero then cell D11 must be zero. If the value in cellD18 is greater than zero than cell D18 must be multiplied by 10%. However if cell D14 & D15 has a value and B18 is less than zero then the following table must be used below using a vlookup, using the cumulative value in col C i.e the 3rd column for F/end GP and 6 th col for B/end GP

However if B18 is greater than zero, then B18 must be multiplied by 10% plus following table must be used below, using the cumulative value in col C i.e the 3rd column

Your assistance will be most appreciated

Reply | Quote

I am looking for a logical formula to include with a lookup function to do the following

If the value in cell D18 is less than zero then cell D11 must be zero. If the value in cellD18 is greater than zero than cell D18 must be multiplied by 10%. However if cell D14 & D15 has a value and B18 is less than zero then the following table must be used below using a vlookup, using the cumulative value in col C i.e the 3rd column for F/end GP and 6 th col for B/end GP

However if B18 is greater than zero, then B18 must be multiplied by 10% plus following table must be used below, using the cumulative value in col C i.e the 3rd column

Your assistance will be most appreciated

Reply | Quote

I am looking for a logical formula to include with a lookup function to do the following

If the value in cell D18 is less than zero then cell D11 must be zero. If the value in cellD18 is greater than zero than cell D18 must be multiplied by 10%. However if cell D14 & D15 has a value and B18 is less than zero then the following table must be used below using a vlookup, using the cumulative value in col C i.e the 3rd column for F/end GP and 6 th col for B/end GP

However if B18 is greater than zero, then B18 must be multiplied by 10% plus following table must be used below, using the cumulative value in col C i.e the 3rd column

Your assistance will be most appreciated

Reply | Quote

I am looking for a logical formula to include with a lookup function to do the following

If the value in cell D18 is less than zero then cell D11 must be zero. If the value in cellD18 is greater than zero than cell D18 must be multiplied by 10%. However if cell D14 & D15 has a value and B18 is less than zero then the following table must be used below using a vlookup, using the cumulative value in col C i.e the 3rd column for F/end GP and 6 th col for B/end GP

However if B18 is greater than zero, then B18 must be multiplied by 10% plus following table must be used below, using the cumulative value in col C i.e the 3rd column

Your assistance will be most appreciated

Reply | Quote

I see a problem, you want to multiply D18 by 1.1 if D18 > 0? This is a looping reference and won’t work in a formula.

cheers, Paul

Reply | Quote

I see a problem, you want to multiply D18 by 1.1 if D18 > 0? This is a looping reference and won’t work in a formula.

cheers, Paul

Reply | Quote

I see a problem, you want to multiply D18 by 1.1 if D18 > 0? This is a looping reference and won’t work in a formula.

cheers, Paul

Reply | Quote

I see a problem, you want to multiply D18 by 1.1 if D18 > 0? This is a looping reference and won’t work in a formula.

cheers, Paul

Reply | Quote

I see a problem, you want to multiply D18 by 1.1 if D18 > 0? This is a looping reference and won’t work in a formula.

cheers, Paul

Reply | Quote

I see a problem, you want to multiply D18 by 1.1 if D18 > 0? This is a looping reference and won’t work in a formula.

cheers, Paul

Reply | Quote

I see a problem, you want to multiply D18 by 1.1 if D18 > 0? This is a looping reference and won’t work in a formula.

cheers, Paul

Reply | Quote

I see a problem, you want to multiply D18 by 1.1 if D18 > 0? This is a looping reference and won’t work in a formula.

cheers, Paul

Reply | Quote

Hi Paul

See attached worksheet, where I have attempted to write a formula to calculate the commision

if B18 <0, then the comm will be =VLOOKUP(B14,S5:U10,3)+vlookup(b15,V5:X10,3)

It would be appreciated if you could assist me

Thanks

Howard

Reply | Quote

Hi Paul

See attached worksheet, where I have attempted to write a formula to calculate the commision

if B18 <0, then the comm will be =VLOOKUP(B14,S5:U10,3)+vlookup(b15,V5:X10,3)

It would be appreciated if you could assist me

Thanks

Howard

Reply | Quote

Hi Paul

See attached worksheet, where I have attempted to write a formula to calculate the commision

if B18 <0, then the comm will be =VLOOKUP(B14,S5:U10,3)+vlookup(b15,V5:X10,3)

It would be appreciated if you could assist me

Thanks

Howard

Reply | Quote

Hi Paul

See attached worksheet, where I have attempted to write a formula to calculate the commision

if B18 <0, then the comm will be =VLOOKUP(B14,S5:U10,3)+vlookup(b15,V5:X10,3)

It would be appreciated if you could assist me

Thanks

Howard

Reply | Quote

Hi Paul

See attached worksheet, where I have attempted to write a formula to calculate the commision

if B18 <0, then the comm will be =VLOOKUP(B14,S5:U10,3)+vlookup(b15,V5:X10,3)

It would be appreciated if you could assist me

Thanks

Howard

Reply | Quote

Hi Paul

See attached worksheet, where I have attempted to write a formula to calculate the commision

if B18 <0, then the comm will be =VLOOKUP(B14,S5:U10,3)+vlookup(b15,V5:X10,3)

It would be appreciated if you could assist me

Thanks

Howard

Reply | Quote

Hi Paul

See attached worksheet, where I have attempted to write a formula to calculate the commision

if B18 <0, then the comm will be =VLOOKUP(B14,S5:U10,3)+vlookup(b15,V5:X10,3)

It would be appreciated if you could assist me

Thanks

Howard

Reply | Quote

Hi Paul

See attached worksheet, where I have attempted to write a formula to calculate the commision

if B18 <0, then the comm will be =VLOOKUP(B14,S5:U10,3)+vlookup(b15,V5:X10,3)

It would be appreciated if you could assist me

Thanks

Howard

Reply | Quote

Howard, I don’t want to seem mean but I am not a fan of downloading other people’s docs / sheets.
In this case I think we can do it in text if you can explain what you want. I find having to explain what I’m doing often leads me to see the problem.

cheers, Paul

Reply | Quote

Howard, I don’t want to seem mean but I am not a fan of downloading other people’s docs / sheets.
In this case I think we can do it in text if you can explain what you want. I find having to explain what I’m doing often leads me to see the problem.

cheers, Paul

Reply | Quote

Howard, I don’t want to seem mean but I am not a fan of downloading other people’s docs / sheets.
In this case I think we can do it in text if you can explain what you want. I find having to explain what I’m doing often leads me to see the problem.

cheers, Paul

Reply | Quote

Howard, I don’t want to seem mean but I am not a fan of downloading other people’s docs / sheets.
In this case I think we can do it in text if you can explain what you want. I find having to explain what I’m doing often leads me to see the problem.

cheers, Paul

Reply | Quote

Howard, I don’t want to seem mean but I am not a fan of downloading other people’s docs / sheets.
In this case I think we can do it in text if you can explain what you want. I find having to explain what I’m doing often leads me to see the problem.

cheers, Paul

Reply | Quote

Howard, I don’t want to seem mean but I am not a fan of downloading other people’s docs / sheets.
In this case I think we can do it in text if you can explain what you want. I find having to explain what I’m doing often leads me to see the problem.

cheers, Paul

Reply | Quote

Howard, I don’t want to seem mean but I am not a fan of downloading other people’s docs / sheets.
In this case I think we can do it in text if you can explain what you want. I find having to explain what I’m doing often leads me to see the problem.

cheers, Paul

Reply | Quote

Howard, I don’t want to seem mean but I am not a fan of downloading other people’s docs / sheets.
In this case I think we can do it in text if you can explain what you want. I find having to explain what I’m doing often leads me to see the problem.

cheers, Paul

Reply | Quote

I am not sure if I have understood exactly what you want, but does the following formula in B11 work?

=IF(B18>0,B18*0.1,IF(AND(B14>0,B15>0),VLOOKUP(B14,S5:U10,3)+VLOOKUP(B15,V5:X10,3),0))

Reply | Quote

I am not sure if I have understood exactly what you want, but does the following formula in B11 work?
=IF(B18>0,B18*0.1,IF(AND(B14>0,B15>0),VLOOKUP(B14,S5:U10,3)+VLOOKUP(B15,V5:X10,3),0))

I’m not sure either, but I think the OP might mean
=B18*0.1+IF(B14,VLOOKUP(B14,S5:U10,3),0)+IF(B15,VLOOKUP(B15,V5:X10,3),0)
(I have made the assumption that cells b14, b15 and b18 will never be negative. Otherwise he won’t be in business long.)

Reply | Quote

Hi Tony

I have attached sample data of a formula which I need amended

if D14 and D15 is blank i.e contains no data and D18 > 0 then Commsion must by calculated using the Comm Function. at present if D14 or D15 has no data the result is “N/A

My current formula is =IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3))

Your assistance in resolving this will be most appreciated

Thanks

Howard

Reply | Quote

Hi Tony

I have attached sample data of a formula which I need amended

if D14 and D15 is blank i.e contains no data and D18 > 0 then Commsion must by calculated using the Comm Function. at present if D14 or D15 has no data the result is “N/A

My current formula is =IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3))

Your assistance in resolving this will be most appreciated

Thanks

Howard

Reply | Quote

Hi Tony

I have attached sample data of a formula which I need amended

if D14 and D15 is blank i.e contains no data and D18 > 0 then Commsion must by calculated using the Comm Function. at present if D14 or D15 has no data the result is “N/A

My current formula is =IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3))

Your assistance in resolving this will be most appreciated

Thanks

Howard

Reply | Quote

Hi Tony

I have attached sample data of a formula which I need amended

if D14 and D15 is blank i.e contains no data and D18 > 0 then Commsion must by calculated using the Comm Function. at present if D14 or D15 has no data the result is “N/A

My current formula is =IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3))

Your assistance in resolving this will be most appreciated

Thanks

Howard

Reply | Quote

Hi Tony

I have attached sample data of a formula which I need amended

if D14 and D15 is blank i.e contains no data and D18 > 0 then Commsion must by calculated using the Comm Function. at present if D14 or D15 has no data the result is “N/A

My current formula is =IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3))

Your assistance in resolving this will be most appreciated

Thanks

Howard

Reply | Quote

Hi Tony

I have attached sample data of a formula which I need amended

if D14 and D15 is blank i.e contains no data and D18 > 0 then Commsion must by calculated using the Comm Function. at present if D14 or D15 has no data the result is “N/A

My current formula is =IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3))

Your assistance in resolving this will be most appreciated

Thanks

Howard

Reply | Quote

Hi Tony

I have attached sample data of a formula which I need amended

if D14 and D15 is blank i.e contains no data and D18 > 0 then Commsion must by calculated using the Comm Function. at present if D14 or D15 has no data the result is “N/A

My current formula is =IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3))

Your assistance in resolving this will be most appreciated

Thanks

Howard

Reply | Quote

Hi Tony

I have attached sample data of a formula which I need amended

if D14 and D15 is blank i.e contains no data and D18 > 0 then Commsion must by calculated using the Comm Function. at present if D14 or D15 has no data the result is “N/A

My current formula is =IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3))

Your assistance in resolving this will be most appreciated

Thanks

Howard

Reply | Quote

Hi John

Have changed my formula to this and it seems to be working

=IF(AND(D14=””,D15=””),Commission(D18+D16,D17),IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),
VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3)))

If there is a better formula, please advise

Regards

Howard

Reply | Quote

Hi John

Have changed my formula to this and it seems to be working

=IF(AND(D14=””,D15=””),Commission(D18+D16,D17),IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),
VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3)))

If there is a better formula, please advise

Regards

Howard

Reply | Quote

Hi John

Have changed my formula to this and it seems to be working

=IF(AND(D14=””,D15=””),Commission(D18+D16,D17),IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),
VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3)))

If there is a better formula, please advise

Regards

Howard

Reply | Quote

Hi John

Have changed my formula to this and it seems to be working

=IF(AND(D14=””,D15=””),Commission(D18+D16,D17),IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),
VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3)))

If there is a better formula, please advise

Regards

Howard

Reply | Quote

Hi John

Have changed my formula to this and it seems to be working

=IF(AND(D14=””,D15=””),Commission(D18+D16,D17),IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),
VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3)))

If there is a better formula, please advise

Regards

Howard

Reply | Quote

Hi John

Have changed my formula to this and it seems to be working

=IF(AND(D14=””,D15=””),Commission(D18+D16,D17),IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),
VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3)))

If there is a better formula, please advise

Regards

Howard

Reply | Quote

Hi John

Have changed my formula to this and it seems to be working

=IF(AND(D14=””,D15=””),Commission(D18+D16,D17),IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),
VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3)))

If there is a better formula, please advise

Regards

Howard

Reply | Quote

Hi John

Have changed my formula to this and it seems to be working

=IF(AND(D14=””,D15=””),Commission(D18+D16,D17),IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),
VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3)))

If there is a better formula, please advise

Regards

Howard

Reply | Quote

I am not sure if I have understood exactly what you want, but does the following formula in B11 work?
=IF(B18>0,B18*0.1,IF(AND(B14>0,B15>0),VLOOKUP(B14,S5:U10,3)+VLOOKUP(B15,V5:X10,3),0))

I’m not sure either, but I think the OP might mean
=B18*0.1+IF(B14,VLOOKUP(B14,S5:U10,3),0)+IF(B15,VLOOKUP(B15,V5:X10,3),0)
(I have made the assumption that cells b14, b15 and b18 will never be negative. Otherwise he won’t be in business long.)

Reply | Quote

I am not sure if I have understood exactly what you want, but does the following formula in B11 work?
=IF(B18>0,B18*0.1,IF(AND(B14>0,B15>0),VLOOKUP(B14,S5:U10,3)+VLOOKUP(B15,V5:X10,3),0))

I’m not sure either, but I think the OP might mean
=B18*0.1+IF(B14,VLOOKUP(B14,S5:U10,3),0)+IF(B15,VLOOKUP(B15,V5:X10,3),0)
(I have made the assumption that cells b14, b15 and b18 will never be negative. Otherwise he won’t be in business long.)

Reply | Quote

I am not sure if I have understood exactly what you want, but does the following formula in B11 work?
=IF(B18>0,B18*0.1,IF(AND(B14>0,B15>0),VLOOKUP(B14,S5:U10,3)+VLOOKUP(B15,V5:X10,3),0))

I’m not sure either, but I think the OP might mean
=B18*0.1+IF(B14,VLOOKUP(B14,S5:U10,3),0)+IF(B15,VLOOKUP(B15,V5:X10,3),0)
(I have made the assumption that cells b14, b15 and b18 will never be negative. Otherwise he won’t be in business long.)

Reply | Quote

I am not sure if I have understood exactly what you want, but does the following formula in B11 work?
=IF(B18>0,B18*0.1,IF(AND(B14>0,B15>0),VLOOKUP(B14,S5:U10,3)+VLOOKUP(B15,V5:X10,3),0))

I’m not sure either, but I think the OP might mean
=B18*0.1+IF(B14,VLOOKUP(B14,S5:U10,3),0)+IF(B15,VLOOKUP(B15,V5:X10,3),0)
(I have made the assumption that cells b14, b15 and b18 will never be negative. Otherwise he won’t be in business long.)

Reply | Quote

I am not sure if I have understood exactly what you want, but does the following formula in B11 work?
=IF(B18>0,B18*0.1,IF(AND(B14>0,B15>0),VLOOKUP(B14,S5:U10,3)+VLOOKUP(B15,V5:X10,3),0))

I’m not sure either, but I think the OP might mean
=B18*0.1+IF(B14,VLOOKUP(B14,S5:U10,3),0)+IF(B15,VLOOKUP(B15,V5:X10,3),0)
(I have made the assumption that cells b14, b15 and b18 will never be negative. Otherwise he won’t be in business long.)

Reply | Quote

I am not sure if I have understood exactly what you want, but does the following formula in B11 work?
=IF(B18>0,B18*0.1,IF(AND(B14>0,B15>0),VLOOKUP(B14,S5:U10,3)+VLOOKUP(B15,V5:X10,3),0))

I’m not sure either, but I think the OP might mean
=B18*0.1+IF(B14,VLOOKUP(B14,S5:U10,3),0)+IF(B15,VLOOKUP(B15,V5:X10,3),0)
(I have made the assumption that cells b14, b15 and b18 will never be negative. Otherwise he won’t be in business long.)

Reply | Quote

I am not sure if I have understood exactly what you want, but does the following formula in B11 work?
=IF(B18>0,B18*0.1,IF(AND(B14>0,B15>0),VLOOKUP(B14,S5:U10,3)+VLOOKUP(B15,V5:X10,3),0))

I’m not sure either, but I think the OP might mean
=B18*0.1+IF(B14,VLOOKUP(B14,S5:U10,3),0)+IF(B15,VLOOKUP(B15,V5:X10,3),0)
(I have made the assumption that cells b14, b15 and b18 will never be negative. Otherwise he won’t be in business long.)

Reply | Quote

Hi Tony

I have sorted out formula as follows

=IF(D18>0,Commission(D18+D16,D17)+IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)),IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))
+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)))

regards

Howard

Reply | Quote

Hi Tony

I have sorted out formula as follows

=IF(D18>0,Commission(D18+D16,D17)+IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)),IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))
+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)))

regards

Howard

Reply | Quote

Hi Tony

I have sorted out formula as follows

=IF(D18>0,Commission(D18+D16,D17)+IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)),IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))
+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)))

regards

Howard

Reply | Quote

Hi Tony

I have sorted out formula as follows

=IF(D18>0,Commission(D18+D16,D17)+IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)),IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))
+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)))

regards

Howard

Reply | Quote

Hi Tony

I have sorted out formula as follows

=IF(D18>0,Commission(D18+D16,D17)+IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)),IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))
+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)))

regards

Howard

Reply | Quote

Hi Tony

I have sorted out formula as follows

=IF(D18>0,Commission(D18+D16,D17)+IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)),IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))
+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)))

regards

Howard

Reply | Quote

Hi Tony

I have sorted out formula as follows

=IF(D18>0,Commission(D18+D16,D17)+IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)),IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))
+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)))

regards

Howard

Reply | Quote

Hi Tony

I have sorted out formula as follows

=IF(D18>0,Commission(D18+D16,D17)+IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)),IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))
+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)))

regards

Howard

Reply | Quote

I am not sure if I have understood exactly what you want, but does the following formula in B11 work?

=IF(B18>0,B18*0.1,IF(AND(B14>0,B15>0),VLOOKUP(B14,S5:U10,3)+VLOOKUP(B15,V5:X10,3),0))

Reply | Quote

I am not sure if I have understood exactly what you want, but does the following formula in B11 work?

=IF(B18>0,B18*0.1,IF(AND(B14>0,B15>0),VLOOKUP(B14,S5:U10,3)+VLOOKUP(B15,V5:X10,3),0))

Reply | Quote

I am not sure if I have understood exactly what you want, but does the following formula in B11 work?

=IF(B18>0,B18*0.1,IF(AND(B14>0,B15>0),VLOOKUP(B14,S5:U10,3)+VLOOKUP(B15,V5:X10,3),0))

Reply | Quote

I am not sure if I have understood exactly what you want, but does the following formula in B11 work?

=IF(B18>0,B18*0.1,IF(AND(B14>0,B15>0),VLOOKUP(B14,S5:U10,3)+VLOOKUP(B15,V5:X10,3),0))

Reply | Quote

I am not sure if I have understood exactly what you want, but does the following formula in B11 work?

=IF(B18>0,B18*0.1,IF(AND(B14>0,B15>0),VLOOKUP(B14,S5:U10,3)+VLOOKUP(B15,V5:X10,3),0))

Reply | Quote

I am not sure if I have understood exactly what you want, but does the following formula in B11 work?

=IF(B18>0,B18*0.1,IF(AND(B14>0,B15>0),VLOOKUP(B14,S5:U10,3)+VLOOKUP(B15,V5:X10,3),0))

Reply | Quote

I am not sure if I have understood exactly what you want, but does the following formula in B11 work?

=IF(B18>0,B18*0.1,IF(AND(B14>0,B15>0),VLOOKUP(B14,S5:U10,3)+VLOOKUP(B15,V5:X10,3),0))

Reply | Quote

Hi Guys

Thanks for the replies to my query. TonyE’s solution does the trick. If B18 is negative, then B11 must ignore the value in B18 and only calculate =VLOOKUP(D14,S4:U10,3)+VLOOKUP(D15,V4:X10,3)

Therefore solution =IF(B18>0,B18*0.1,IF(AND(B14>0,B15>0),VLOOKUP(B14,S5:U10,3)+VLOOKUP(B15,V5:X10,3),0)) provided by TonyE works

Once again thanks for all the help from the other loungers as well

Regards

Howard

Reply | Quote

Hi Guys

Thanks for the replies to my query. TonyE’s solution does the trick. If B18 is negative, then B11 must ignore the value in B18 and only calculate =VLOOKUP(D14,S4:U10,3)+VLOOKUP(D15,V4:X10,3)

Therefore solution =IF(B18>0,B18*0.1,IF(AND(B14>0,B15>0),VLOOKUP(B14,S5:U10,3)+VLOOKUP(B15,V5:X10,3),0)) provided by TonyE works

Once again thanks for all the help from the other loungers as well

Regards

Howard

Reply | Quote

Hi Guys

Thanks for the replies to my query. TonyE’s solution does the trick. If B18 is negative, then B11 must ignore the value in B18 and only calculate =VLOOKUP(D14,S4:U10,3)+VLOOKUP(D15,V4:X10,3)

Therefore solution =IF(B18>0,B18*0.1,IF(AND(B14>0,B15>0),VLOOKUP(B14,S5:U10,3)+VLOOKUP(B15,V5:X10,3),0)) provided by TonyE works

Once again thanks for all the help from the other loungers as well

Regards

Howard

Reply | Quote

Hi Guys

Thanks for the replies to my query. TonyE’s solution does the trick. If B18 is negative, then B11 must ignore the value in B18 and only calculate =VLOOKUP(D14,S4:U10,3)+VLOOKUP(D15,V4:X10,3)

Therefore solution =IF(B18>0,B18*0.1,IF(AND(B14>0,B15>0),VLOOKUP(B14,S5:U10,3)+VLOOKUP(B15,V5:X10,3),0)) provided by TonyE works

Once again thanks for all the help from the other loungers as well

Regards

Howard

Reply | Quote

Hi Guys

Thanks for the replies to my query. TonyE’s solution does the trick. If B18 is negative, then B11 must ignore the value in B18 and only calculate =VLOOKUP(D14,S4:U10,3)+VLOOKUP(D15,V4:X10,3)

Therefore solution =IF(B18>0,B18*0.1,IF(AND(B14>0,B15>0),VLOOKUP(B14,S5:U10,3)+VLOOKUP(B15,V5:X10,3),0)) provided by TonyE works

Once again thanks for all the help from the other loungers as well

Regards

Howard

Reply | Quote

Hi Guys

Thanks for the replies to my query. TonyE’s solution does the trick. If B18 is negative, then B11 must ignore the value in B18 and only calculate =VLOOKUP(D14,S4:U10,3)+VLOOKUP(D15,V4:X10,3)

Therefore solution =IF(B18>0,B18*0.1,IF(AND(B14>0,B15>0),VLOOKUP(B14,S5:U10,3)+VLOOKUP(B15,V5:X10,3),0)) provided by TonyE works

Once again thanks for all the help from the other loungers as well

Regards

Howard

Reply | Quote

Hi Guys

Thanks for the replies to my query. TonyE’s solution does the trick. If B18 is negative, then B11 must ignore the value in B18 and only calculate =VLOOKUP(D14,S4:U10,3)+VLOOKUP(D15,V4:X10,3)

Therefore solution =IF(B18>0,B18*0.1,IF(AND(B14>0,B15>0),VLOOKUP(B14,S5:U10,3)+VLOOKUP(B15,V5:X10,3),0)) provided by TonyE works

Once again thanks for all the help from the other loungers as well

Regards

Howard

Reply | Quote

Hi Guys

Thanks for the replies to my query. TonyE’s solution does the trick. If B18 is negative, then B11 must ignore the value in B18 and only calculate =VLOOKUP(D14,S4:U10,3)+VLOOKUP(D15,V4:X10,3)

Therefore solution =IF(B18>0,B18*0.1,IF(AND(B14>0,B15>0),VLOOKUP(B14,S5:U10,3)+VLOOKUP(B15,V5:X10,3),0)) provided by TonyE works

Once again thanks for all the help from the other loungers as well

Regards

Howard

Reply | Quote

One possible workaround is to check it for “N/A”. Try this :

Code:

=IF(ISERROR(IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3))),0,IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3)))

Reply | Quote

One possible workaround is to check it for “N/A”. Try this :

Code:

=IF(ISERROR(IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3))),0,IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3)))

Reply | Quote

One possible workaround is to check it for “N/A”. Try this :

Code:

=IF(ISERROR(IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3))),0,IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3)))

Reply | Quote

One possible workaround is to check it for “N/A”. Try this :

Code:

=IF(ISERROR(IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3))),0,IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3)))

Reply | Quote

One possible workaround is to check it for “N/A”. Try this :

Code:

=IF(ISERROR(IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3))),0,IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3)))

Reply | Quote

One possible workaround is to check it for “N/A”. Try this :

Code:

=IF(ISERROR(IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3))),0,IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3)))

Reply | Quote

One possible workaround is to check it for “N/A”. Try this :

Code:

=IF(ISERROR(IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3))),0,IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3)))

Reply | Quote

One possible workaround is to check it for “N/A”. Try this :

Code:

=IF(ISERROR(IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3))),0,IF(D18>0,Commission(D18+D16,D17)+VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3),VLOOKUP(D14,Front_end_Dale,3)+VLOOKUP(D15,BackEnd_dale,3)))

Reply | Quote

Hi Howard
You may already know this but a trick to keeping long monster formulas readable is to use ALT + ENTER to break up the formula on multiple lines.
So something like your:
=IF(D18>0,Commission(D18+D16,D17)+IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)),IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))
+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)))
Can be read as
=IF(D18>0,Commission(D18+D16,D17)+ ALT + ENTER
IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+ ALT + ENTER
IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)), ALT + ENTER
IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+ ALT + ENTER
IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3))) ALT + ENTER

ALT + ENTER produces an line break character that doesn’t change the formula.
Its’ even nicer in Excel 2007, because the formula bar can be resized.

Reply | Quote

Hi Howard
You may already know this but a trick to keeping long monster formulas readable is to use ALT + ENTER to break up the formula on multiple lines.
So something like your:
=IF(D18>0,Commission(D18+D16,D17)+IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)),IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))
+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)))
Can be read as
=IF(D18>0,Commission(D18+D16,D17)+ ALT + ENTER
IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+ ALT + ENTER
IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)), ALT + ENTER
IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+ ALT + ENTER
IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3))) ALT + ENTER

ALT + ENTER produces an line break character that doesn’t change the formula.
Its’ even nicer in Excel 2007, because the formula bar can be resized.

Reply | Quote

Hi Howard
You may already know this but a trick to keeping long monster formulas readable is to use ALT + ENTER to break up the formula on multiple lines.
So something like your:
=IF(D18>0,Commission(D18+D16,D17)+IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)),IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))
+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)))
Can be read as
=IF(D18>0,Commission(D18+D16,D17)+ ALT + ENTER
IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+ ALT + ENTER
IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)), ALT + ENTER
IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+ ALT + ENTER
IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3))) ALT + ENTER

ALT + ENTER produces an line break character that doesn’t change the formula.
Its’ even nicer in Excel 2007, because the formula bar can be resized.

Reply | Quote

Hi Howard
You may already know this but a trick to keeping long monster formulas readable is to use ALT + ENTER to break up the formula on multiple lines.
So something like your:
=IF(D18>0,Commission(D18+D16,D17)+IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)),IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))
+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)))
Can be read as
=IF(D18>0,Commission(D18+D16,D17)+ ALT + ENTER
IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+ ALT + ENTER
IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)), ALT + ENTER
IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+ ALT + ENTER
IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3))) ALT + ENTER

ALT + ENTER produces an line break character that doesn’t change the formula.
Its’ even nicer in Excel 2007, because the formula bar can be resized.

Reply | Quote

Hi Howard
You may already know this but a trick to keeping long monster formulas readable is to use ALT + ENTER to break up the formula on multiple lines.
So something like your:
=IF(D18>0,Commission(D18+D16,D17)+IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)),IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))
+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)))
Can be read as
=IF(D18>0,Commission(D18+D16,D17)+ ALT + ENTER
IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+ ALT + ENTER
IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)), ALT + ENTER
IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+ ALT + ENTER
IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3))) ALT + ENTER

ALT + ENTER produces an line break character that doesn’t change the formula.
Its’ even nicer in Excel 2007, because the formula bar can be resized.

Reply | Quote

Hi Howard
You may already know this but a trick to keeping long monster formulas readable is to use ALT + ENTER to break up the formula on multiple lines.
So something like your:
=IF(D18>0,Commission(D18+D16,D17)+IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)),IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))
+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)))
Can be read as
=IF(D18>0,Commission(D18+D16,D17)+ ALT + ENTER
IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+ ALT + ENTER
IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)), ALT + ENTER
IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+ ALT + ENTER
IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3))) ALT + ENTER

ALT + ENTER produces an line break character that doesn’t change the formula.
Its’ even nicer in Excel 2007, because the formula bar can be resized.

Reply | Quote

Hi Howard
You may already know this but a trick to keeping long monster formulas readable is to use ALT + ENTER to break up the formula on multiple lines.
So something like your:
=IF(D18>0,Commission(D18+D16,D17)+IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)),IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))
+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)))
Can be read as
=IF(D18>0,Commission(D18+D16,D17)+ ALT + ENTER
IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+ ALT + ENTER
IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)), ALT + ENTER
IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+ ALT + ENTER
IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3))) ALT + ENTER

ALT + ENTER produces an line break character that doesn’t change the formula.
Its’ even nicer in Excel 2007, because the formula bar can be resized.

Reply | Quote

Hi Howard
You may already know this but a trick to keeping long monster formulas readable is to use ALT + ENTER to break up the formula on multiple lines.
So something like your:
=IF(D18>0,Commission(D18+D16,D17)+IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)),IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))
+IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)))
Can be read as
=IF(D18>0,Commission(D18+D16,D17)+ ALT + ENTER
IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+ ALT + ENTER
IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3)), ALT + ENTER
IF(D14=””,0,VLOOKUP(D14,Front_end_Dale,3))+ ALT + ENTER
IF(D15=””,0,VLOOKUP(D15,BackEnd_dale,3))) ALT + ENTER

ALT + ENTER produces an line break character that doesn’t change the formula.
Its’ even nicer in Excel 2007, because the formula bar can be resized.

Reply | Quote