IF conditions met then Calcluation is performed.

[Rahuluk]

Fellow ExcelERs,

I am trying to write a formula whereby if a certain value is selected in a cell from a dropdown list it is then looked up in an array table and there will be a value (%) in index column 2 and a calculation is performed base on it being multiplied by the value of another cell. However if a lookup value needs to have a return value of <=1 (max £1.00) then how can I incorporate this into the formula.

A lookup calculation of a certain value maybe £1.25 but I would like the value returned to be £1.00. If the same lookup value calculation generates a value of £0.75 then this will remain as the value.

What I am trying to achieve is an accounting spreadsheet where certain costs where selling promotions are applied a MAX selling fee regardless of the actual selling fee will be applied.

Many thanks on any thoughts.

Rahul

 

[Dave87]

Ok, difficult to know what you want without seeing the layout but something like this

=IF(A1>=1,VLOOKUP(1,C1:D12,2,FALSE),VLOOKUP(A1,C1:D12,2,FALSE))

where
A1 = your dropdown value/lookup value
C1:D12 = is your lookup range

 

[Butty578]

Do you have any data or formula you're currently working on?
=IF(A1>=1,"1", A1)
This was working, assuming you're using A1 as your reference point, and needing the figure in a different cell

 

[Rahuluk]

Ok, difficult to know what you want without seeing the layout but something like this

=IF(A1>=1,VLOOKUP(1,C1:D12,2,FALSE),VLOOKUP(A1,C1:D12,2,FALSE))

where
A1 = your dropdown value/lookup value
C1:D12 = is your lookup range

Hi Dave87,

Thanks for your reply. Just so that you have more information on what I am currently inputting see attached image.
The formula is in N9.
Say for example the BLUE CELL selected is EBY PROMO, if this is the case the FINAL SALE VALUE must be multiplied by the lookup value N24 15%. However, if this calculation meant the value is greater than £1.00 then I would like the PLATFORM FEE to remain at MAX £1.00. If for example the calculation retrieved less than £1.00 (i.e. £0.75) then I would like the lower value to be set.

If for example the BLUE CELL was EBY (this is a dropdown list) then the lookup value for the calculation is set to 9% however for this Selected platform I DO NOT want the £1.00 condition applied. If the calculation is >£1.00 then that's fine. This condition I want only for EBY PROMO.

I hope this is clear now.

Thank you for your prompt reply.

Rahul

 

[Rahuluk]

Hi Butty578

Thanks for your reply. Just so that you have more information on what I am currently inputting see attached image.
The formula is in N9.
Say for example the BLUE CELL selected is EBY PROMO, if this is the case the FINAL SALE VALUE must be multiplied by the lookup value N24 15%. However, if this calculation meant the value is greater than £1.00 then I would like the PLATFORM FEE to remain at MAX £1.00. If for example the calculation retrieved less than £1.00 (i.e. £0.75) then I would like the lower value to be set.

If for example the BLUE CELL was EBY (this is a dropdown list) then the lookup value for the calculation is set to 9% however for this Selected platform I DO NOT want the £1.00 condition applied. If the calculation is >£1.00 then that's fine. This condition I want only for EBY PROMO.

I hope this is clear now.

Thank you for your prompt reply.

Rahul
View attachment 17175

 

[Dave87]

Ok so thats a different question than you asked before, try this formula. Why multiply by 1.2? i've kept it in assuming its required...

=IF(I9="EBY PROMO",IF(VLOOKUP(I9,$M$22:$N$23,2,FALSE)*L9*1.2>1,1,VLOOKUP(I9,$M$22:$N$23,2,FALSE)*L9*1.2),(VLOOKUP(I9,$M$22:$N$23,2,FALSE)*L9*1.2))

 

[Rahuluk]

Ok so thats a different question than you asked before, try this formula. Why multiply by 1.2? i've kept it in assuming its required...

=IF(I9="EBY PROMO",IF(VLOOKUP(I9,$M$22:$N$23,2,FALSE)*L9*1.2>1,1,VLOOKUP(I9,$M$22:$N$23,2,FALSE)*L9*1.2),(VLOOKUP(I9,$M$22:$N$23,2,FALSE)*L9*1.2))

Hi Dave,

The multiplication by 1.2 is to factor in VAT element. As the Lookup value is a drop list and therefore not always selected will it return a #N/A error. How can we get rid of this?

Thanks
Rahul

 

[Dave87]

what do you want to happen when the drop down list is blank?

 

[Rahuluk]

what do you want to happen when the drop down list is blank?

Keep the cell Empty

 

[Dave87]

try

=IFERROR(IF(I9="EBY PROMO",IF(VLOOKUP(I9,$M$22:$N$23,2,FALSE)*L9*1.2>1,1,VLOOKUP(I9,$M$22:$N$23,2,FALSE)*L9*1.2),(VLOOKUP(I9,$M$22:$N$23,2,FALSE)*L9*1.2)),0)