If function doesn't give the answer I want !

certificates

Board Regular
Joined
Apr 20, 2009
Messages
141
Office Version
  1. 2013
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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,283
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Rich (BB code):
=iferror(IF(L67/22.5,SUM('September 2020:October 2019'!M67),"Error") ,0)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,389
Office Version
  1. 365
Platform
  1. Windows
=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"?
 

certificates

Board Regular
Joined
Apr 20, 2009
Messages
141
Office Version
  1. 2013

ADVERTISEMENT

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.
 

certificates

Board Regular
Joined
Apr 20, 2009
Messages
141
Office Version
  1. 2013

ADVERTISEMENT

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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,297
Office Version
  1. 365
Platform
  1. Windows
Is this what you mean
=IF(L67/22.5=SUM('September 2020:October 2019'!M67),0,"Error")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,297
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,285
Messages
5,836,421
Members
430,428
Latest member
Deepak Tanwar

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
Top