Blank cells returning a 0

eraust

New Member
Joined
Sep 13, 2022
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Hi All

I have a workbook which I am trying to link to another using INDEX MATCH.

The cells I am asking INDEX and MATCH to return have different values in them and some of them are blank. Some, but not all, of the blank cells are returning zeros rather than a blank cell.

I've tried to check the formatting of the cells to ensure they're all the same and they seem to be. I've also tried adding a conditional formatting rule using ISBLANK selecting the custom format category and entering a semicolon in the type field, but some of my cells are still returning zeros. Any ideas?

Thanks in advance
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Ok I have just come across the 'Show zeros in blank cells' option in the advanced files option, so I have solved this.

That took me longer than it should have to find out and I was obviously very much overcomplicating things.
 
Upvote 0
Hi All - bringing this back as I need a bit of help.

I've used the 'Show zeros in blank cells' option on one of my work sheets but turns out this option thinks that 00:00 (as in, midnight) is a zero and is now showing me blank cells there where I need it to say midnight. The 00:00 cells are formatted Custom, hh:mm and I can't change them to 24:00 as some of my formulas involve time calculations.

Any ideas? Thanks in advance.
 
Upvote 0
Without a screenshot, it appears an IF statement might be needed. Let me know if you can incorporate the following into the 1st Cell in your formulas (assuming Column headers), where B2 refers to the first Cell in Columns with 0. If this doesn't work, please add a screenshot. Thanks!
=IF(B2=0,"midnight",B2)
 
Upvote 0
another thought. Technically 00:00 is not 24:00 as 24:00 doesn't exist. The last second of a day is 23:59:59, one second later the time is 00:00:00, but the day (an integer as far as Excel is concerned) has incremented by 1. If this is important (it presumably is!) you need to consider the date component of the cells and you can identify this by using 'd hh:mm' as the custom format.
 
Upvote 0

Forum statistics

Threads
1,223,952
Messages
6,175,596
Members
452,658
Latest member
GStorm

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