#NUM! Help

[Asgard]

Hello all ,
Looking for a bit of help on a small problem i am having. how do i stop the following returning the #NUM! error ?. =IF(AGGREGATE(14,6,--LEFT(H3:H19999,3),1) and a 0 if ther is no value in the cell ?. I have been looking around at the IF(ISERROR, but i think i may be on the wrong path. Any help greatly appreciated

 

[Mel Smith]

Maybe =iferror(if(AGGREGATE (14,6,--LEFT(H3:H19999,3,1),"")

Untested but might be what you're after.

HTH

Mel

 

[Asgard]

Thanks for the reply Mel, but did not work

 

[Canapone]

Hi,

maybe:

=AGGREGATE(14,6,--LEFT(H3:H19999+0,3),1)

Regards

 

[Asgard]

HI,
Started with this formula and this gets the #NUM! error. Thanks for the reply though.

 

[Aladin Akyurek]

@Asgard

What is the purpose of this AGGREGATE formula?

 

[Asgard]

Hello Aladin,
The work sheet has a column that requires a entry in the Format 123/16 the aggregatr formula just strips away "/16" and passes the value to second cell where it is compared to the same value in a second spread sheet and then has a 1 added to the value which is passed to a cell in both worksheets.