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


8 Replies


Let's say the value is in A1



@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


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


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

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


It should work:


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.