formula issue in excel

New Contributor

Hi, can you please tell me the formula to calculate the following.

 

If the cell value is 170 or less than 170 I want the answer to be 0.  and if the cell is greater than 170 I want 170 deducted from the cell amount and the difference reported.

 

Can you kindly advise?

 

Thank you in advance

Niki

8 Replies

@Niki_1360 

Let's say the value is in A1

 

=MAX(A1-170,0)

@Hans Vogelaar 

 

Thank you for your help but that is only part of the calculation I am trying to do and need to incorporate into the rest of the formula but I cannot get it to work

@Niki_1360 

We may be able to help you if you provide enough information.

Hi Hans,
What I am trying to do is create a formula that if the subject cell is greater than £170 to then deduct £170 from the subject cell and report the difference - or if the subject cell is £170 or less to report £0

@Niki_1360 

That is precisely what the formula I posted does, but you wrote that you had trouble incorporating it. What exactly is the problem?

You could try

=IF(A1=170,0,"Not £170")

Obviously you can change the "Not £170" to whatever you need it to say

=IF(A1=170,0,sum(A1-A2))
Presuming A1 is the total box and A2 is the price
Hi Hans, I have done an example but no option to upload. Say if the cell value is £500 I want £170 deducted from that and the difference of £330 reported which is straight forward but it also needs to account for an amount being £170 or less and if so to be reported as £0
I have tested your formula but it does not work in this instance

@Niki_1360 

It should work:

S1062.png

Could you create a stripped-down copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.