IF Function?

[RobbiiuK]

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?

 

[Fluff]

Hi & welcome to MrExcel.
How about

=F14+IF(E38="Y",D34*1.1,D34)*(D34>0)

 

[Mr_A]

This should do it.

=IF(D34>0,IF(E38="Y",F14+(D34*1.1),F14+D34),D5)

 

[Fluff]

That won't work if the absolute value in D34 is less than F14.

 

[RobbiiuK]

Hi & welcome to MrExcel.
How about

=F14+IF(E38="Y",D34*1.1,D34)*(D34>0)

Great, that works brilliantly - thanks so much!

I have another issue I'm struggling with, it's probably basic math and an easy enough formula (famous last words!) but sometimes our cost is a negative sum due to a buy-back scheme that we operate.

Usually, to work out the cost total would be to add both but when this negative sum is calculated, it should still come off our cost price but instead it adds, it can become easily confusing - as an example.

Cost A - £3,336.00
Cost B is -£56.07

In excel, the total cost is worked out as £3,392.07
> However, it should be £3,279.93

Cost B is variable depending on the buy-back value.

What formula could be used so if it were a negative sum in Cost B, it subtracts from Cost A opposed to adding, while adding two figures still as normal if they're both 'positive sums'?

 

[Fluff]

As this is a significantly different question, It needs a new thread. Thanks