If function doesn't give the answer I want !

L

Legacy 116183

Guest
If I have this for a total of twelve monthly sheets

=IF(L67/22.5,SUM('September 2020:October 2019'!M67),"Error")

Currently, there isn't anything in any of the M67 cells and the function shows Error in the cell. If there were any sales at £22.50 the formula shows the correct answer.

Putting a £0.00 in the monthly M67 cells makes no difference, yet the two formulas L67/22.5 and SUM('September 2020:October 2019'!M67 both give £0.00 as a result.

How can I adjust this so that the result is £0.00 as it should be.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Rich (BB code):
=iferror(IF(L67/22.5,SUM('September 2020:October 2019'!M67),"Error") ,0)
 
Upvote 0
=IF(L67/22.5,SUM('September 2020:October 2019'!M67),"Error")
This formula says that if L67/22.5 is zero then return "Error" and you have said that L67/22.5 is zero ...

L67/22.5 .... give £0.00 as a result.

... so the formula is doing exactly what you have told it.

What I don't know is what you do want returned if L67 is zero if you don't want "Error"?
 
Upvote 0
If the sums don't match I want it to show error, as this is a check, but the sums do match an still error is shown

The answer should be £0.00 in this case.

The rows either side work fine, it is just this one without any sales at that price yet.
 
Upvote 0
If want it to show £0.00 where it currently shows error. The formula is =IF(L67/22.5,SUM('September 2020:October 2019'!M67),"Error")

It gives the answer error, though L67/22.5 does = the sum of all the cells M67.

I want it to show 0

The formula works for the cells either side.

1594570842773.png
 
Upvote 0
Is this what you mean
=IF(L67/22.5=SUM('September 2020:October 2019'!M67),0,"Error")
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top