# Reference cell formula returning 0 when BLANK, need BLANK to show

1. ## Reference cell formula returning 0 when BLANK, need BLANK to show

I'm trying to pull data from another excel sheet - my problem is that when I use my formula < =VALUE('Sheet 1'!AH5) > and AH5 is blank, it is filling in a zero in the new cell. This is a problem because I am using the referenced data in an AVERAGE formula and it is throwing off my numbers (because the zero is counted in the average instead of being left blank). Is there any way I can pull the information from the other sheet and have it fill in blanks when the original cells are blanks?

2. ## Re: Reference cell formula returning 0 when BLANK, need BLANK to show

You can do this

=IFERROR(1/(1/VALUE('sheet 1'AH5)), "")

3. ## Re: Reference cell formula returning 0 when BLANK, need BLANK to show

This may be obvious (I'm still new to Excel), but would I add that to my existing formula or make it the new formula? If I add/combine it with the one I have, how do I do that?

5. ## Re: Reference cell formula returning 0 when BLANK, need BLANK to show

Thank you. So that worked with the blanks. The problem I'm having now is that the zero's are also showing up as blanks, and I need the zero's to show up as zero's. How would I alter that to show zero as zero and blank as blank?

6. ## Re: Reference cell formula returning 0 when BLANK, need BLANK to show

If you have to differentiate between blank and 0, try

IF('sheet 1'AH5="","",VALUE('sheet 1'AH5))

7. ## Re: Reference cell formula returning 0 when BLANK, need BLANK to show

As an aside, why are you using VALUE? Are some (or all) of the numbers on sheet 1 set as text? Do you have some text values that you want to show up as errors?

If all the data is numbers (or blanks)
=IF('sheet 1'AH5="","",'sheet 1'AH5)

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1