If function doesn't give the answer I want !

certificates

Board Regular
Joined
Apr 20, 2009
Messages
127
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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,179
Office Version
  1. 2016
  2. 2013
  3. 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
48,557
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
127

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
127

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
56,856
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
56,856
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,117
Messages
5,640,206
Members
417,131
Latest member
Seanr19871

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