Hi,
I'm not sure if I'm using the correct function, or if I'll even explain this correctly - but I'll try my best..!
I basically have two criterias then I want to meet, it's to work out the sales total of an order based on whether a finance option is required or not - if so, we add 10% margin across the finance period.
Currently I have =IF(E38="Y",F14+(D34*1.1),F14+D34).
Cell E38 is a drop down option for Y/N on a finance required field.
If Y, then we want the formula to add up the licensing total (cell F14) and the hardware cost (D34) with 10% mark up.
If N, then simply add up the figures from F14 and D34 without the 10%.
This works, however, sometimes we can offset the hardware using a fund we create from commission earned and therefore the hardware cost can become a negative sum.
When the hardware is a negative sum, we want ignore the hardware cost as this then becomes deducted from the sales total and shows an incorrect figure, in this case, we just want to have the sales total showing the licensing cost (F14) - basically removing/skipping the value of D34 from the above calculation if it's less than £0.00 (negative sum).
How can I introduce this to the formula?
I'm not sure if I'm using the correct function, or if I'll even explain this correctly - but I'll try my best..!
I basically have two criterias then I want to meet, it's to work out the sales total of an order based on whether a finance option is required or not - if so, we add 10% margin across the finance period.
Currently I have =IF(E38="Y",F14+(D34*1.1),F14+D34).
Cell E38 is a drop down option for Y/N on a finance required field.
If Y, then we want the formula to add up the licensing total (cell F14) and the hardware cost (D34) with 10% mark up.
If N, then simply add up the figures from F14 and D34 without the 10%.
This works, however, sometimes we can offset the hardware using a fund we create from commission earned and therefore the hardware cost can become a negative sum.
When the hardware is a negative sum, we want ignore the hardware cost as this then becomes deducted from the sales total and shows an incorrect figure, in this case, we just want to have the sales total showing the licensing cost (F14) - basically removing/skipping the value of D34 from the above calculation if it's less than £0.00 (negative sum).
How can I introduce this to the formula?