Hiding #VALUE

[tghcogo]

Hi


I'm trying to hide a #VALUE return using IF(ISERROR(formula),"",formula),
but excel doesn't like it.

the #VALUE is returned by =IF(F14<>0,F14-O14-P14,"") when there is no value in O14

the formula in O14 is =IF(SUMIF(Purchases!$E$6:$E$999,$D14,Purchases!$I$6:$I$999)<>0,SUMIF(Purchases!$E$6:$E$999,$D14,Purchases!$I$6:$I$999),"")

When I try =IF(ISERROR(IF(F14<>0,F14-O14-P14,""),"",(IF(F14<>0,F14-O14-P14,"")

Excel bounces the formula highlighting the first P14 as the problem, but this is either a blank cell or has a numeric value.

My brain is addled what am I doing wrong?

[Richard Buttrey]

Hi,

Upload your workbook, or at least the relevant part of it so that we can see your problem in context.

Rgds

[DonkeyOte]

A Null ("") is a 0 length text string which unaltered can not be coerced.

Try:

Please Login or Register  to view this content.

However, it would IMO make more sense to mask the zero returns (of source cells - eg: SUMIF output) via formatting route, thereby keeping underlying data types consistent.

[tghcogo]

Thaks for your replies.

Basically what I am trying to do is to pick up purchases that relate to a job number on another worksheet. I'll upload the relevant part of the workbook.

[tghcogo]

Firts attempt to upload didn't work, let's try again

[Richard Buttrey]

Hi,

Use "0" instead of " "" "

Regards

[DonkeyOte]

Or, try the formula suggested earlier.
That formula added to N14 on Contracts and applied to remainder of column would appear to generate the desired results, no ?

[tghcogo]

Unfortunately no, it needs to sum the values of purchases on the purchases worksheet against the job no allocated on the contracts worksheet. It produces the #value error if there is no value derived by the formula in column O, and that is what I'm trying to correct.

[DonkeyOte]

Post a sample with the suggested formula in place and the errors you describe.

[Richard Buttrey]

Hi,

But if you put a zero in the Contracts column O formulae as I suggested earlier you'll find column R does evalaute. Did you try this?

e.g.

Please Login or Register  to view this content.

Rgds

[DonkeyOte]

@Richard, if you're putting a zero in as default there's no need for the double evaluation at all.

Not saying I disagree but this was the point made re: using formatting to mask 0's rather than returning Null and in turn having mixed data types.

The formula suggested previously will account for the Nulls, OP implies otherwise hence I ask for a copy to illustrate.

[tghcogo]

Thanks for your patience, the objective was to have the cell blank unless there was a value derived, so a zero is not really an option.

I've uploaded as requested, the cell to look at is R6, the formula appears to be in as text, but when you try to edit it, it goes "live" and and pressing enter brings up the dialogue box, "your formula contains an error........"


Thanks

[DonkeyOte]

Below is the formula from way back in post # 3 (originally for R14 [to be copied throughout column])

Please Login or Register  to view this content.

The results of the above would be:

Please Login or Register  to view this content.

and just to continue the theme of sounding like a broken record ... you should really be letting results of precedent cells be 0 (rather than Null) and using a Custom Format to mask the 0s
(as is you're making your life far more difficult for yourself than is necesssary)

[tghcogo]

Well I'll be.......

I must have made a typo when trying your original fix, hence going round in circles.

Many thanks for your patience and persistance it works perfectly.

I'm not sure how th N works in the formula but it does. So how would you mask the 0 with custom format?

[tghcogo]

I'm trying to mark the thread solved but can't find the "prefix dropdown"

[DonkeyOte]

N(): in effect returns number values / booleans as number and anything else as 0 (excluding underlying errors)

Formula Nulls are 0 length text strings and they can not be coerced to a number directly, eg:

Please Login or Register  to view this content.

By using N to convert the Null prior to coercion we avoid the #VALUE! error.

Please Login or Register  to view this content.

In this instance you could equally use SUM as we did for O6:P6 where you have the potential for the same problem (Nulls in O)
(we used SUM there as you had a contiguous range to aggregate)

Re; Custom Number Format ex. using O

Please Login or Register  to view this content.

Custom Format applied to O6:O999 of: #,##0.00;-#,##0.00;;@

at this point the 0 persists but is not visible / does not print.

Effect of this is that you are no longer mixing data types - Column O will always contain a number (and not mix of numbers and Nulls)
You always remove the need to potentially calculate the SUMIF twice over.