Auto Deduct Using Formula

[tasha23xx]

Hi,

I am fairly new to formulas so hoping someone can help me with the following:

On my invoicing sheet I have the invoice amount in column D formatted like this: £1202.02 (I manually enter this)

In column K I have notes which are formatted like this: <style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>Credit Note Assigned 8088 £10 (being pulled through via a vlookup from credit note workbook)

In column L I have the credit note amount formatted like this: £10.00 ((being pulled through via a vlookup from credit note workbook)

What I want it to do:

If a cell within column K is not blank it deducts the amount in column L off the amount in column D otherwise it does nothing to the amount within column D

Any suggestions?

Thanks

 

[Yongle]

How about

=IF(ISNUMBER(K2),D2-L2,D2)

or

=IF(ISBLANK(K2),D2,D2-L2)

or even

=CHOOSE(ISBLANK(K2)+1,D2-L2,D2)

 

[tasha23xx]

How about

=IF(ISNUMBER(K2),D2-L2,D2)

or

=IF(ISBLANK(K2),D2,D2-L2)

or even

=CHOOSE(ISBLANK(K2)+1,D2-L2,D2)

Hi, thanks for these, all of them work as expected but i am putting the formula in an empty column so I can drag it down, but it is returning the results in this column...I need it to automatically adjust the price within column D but I can not put the formula in there as that needs to be the amount which I enter manually.

Any suggestions on this final part, hope this makes sense?

Thanks

 

[Yongle]

It is not possible have a constant value AND a formula in the same cell.
So you need to find another way to achieve what you want

The value in column D is required for the calculation. Do you not want it displayed?

Option 1
Insert a column between D and E
- put the formula in the new column
- hide column D and unhide it when you want to input values

Option2
Use a column at the extreme right of all the columns you are currently using
- input your value in there
- put the formula (with references adjusted for the new column) in column D

Option3
Use one sheet for all the input and calculations and another sheet to carry the values that you want
- formulas can be used to carry the values from one sheet to another so that input is required once only

If none of these options appeal, then you need to explain more fully what you are trying to achieve, what should be displayed and what shouldn't etc