• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Min with =NA() in Range = #N/A

S

SteveT

New Member
Hi All,


I have a dynamic chart range for 2 series. The chart range 1 has data that goes back 3 years. Chart range 2 only has data for the last year. I allow users to pick how far back they want to see the chart 1 month to 36 months. So if they choose more than a years worth of data i have NA() in the 2nd series so that it will not show on the chart instead of a zero line for 2 years back.


Along with the original chart series lines, I want to highlight the min and max of each series by using a couple of dummy series mostly with NA() and then the Min of the dynamic range chosen.


However, when I put in the Min function for the series that doesn't have 3 years worth of data, and the range selected includes an NA(), the Min Function returns NA() as the Min and therefore no min shows up on my chart.


To replicate this, put an =NA() in A1, 1 in A2 and 3 in A3 then in A4 put =min(a1:a4) and you will get an #N/A.


Do you know of any other way to get around this? The array function i came up with worked in one cell, but not in a range of cells for the dynamic user choice.
 
Thanks luke. If i put that array in b1 and fill it across b1:b4, how can i get A1,A3 and A4 to =#N/A and have A2=that Min only?
 
=MIN(IF(ISNUMBER(A2:A4),A2:A4,NA())) still = 1 in all rows when i would like it to be


b1 = #n/a

b2 = 1

b3 = #n/a

b4 = #n/a


Since this will be used to highlight a chart data point for the min across the series at a specific time point.
 
Thanks Indian. I like where you are going with the formula, but that gives me:


b1 = #n/a

b2 = #n/a

b3 = #n/a


If i modify it as such: =IF(ISERROR(A1:A3),NA(),MIN(IF(ISNUMBER(A1:A3),A1:A3))) i get:


b1 = #n/a

b2 = 1

b3 = 1


Closer but would want b3=#n/a since a3 doesnot equal the min
 
Thanks for all your help.


Think i got it:


=IF(ISERROR(A1:A3),NA(),IF((A1:A3)=MIN(IF(ISNUMBER(A1:A3),A1:A3)),A1:A3,NA()))
 
When you copy across, are you including the absolute references? Indian's formula would be:

=IF(ISERROR(A1),NA(),MIN(IF(ISNUMBER($A1:$A3),$A1:$A3)))
 
I still get NA for all values


However the formula i posted above seems to work fine.


Would love a none array fomula, but this should work.


=IF(ISERROR(A1:A3),NA(),IF((A1:A3)=MIN(IF(ISNUMBER(A1:A3),A1:A3)),A1:A3,NA()))


entered as an array in cells B1:b3
 
Well, if it's working, then sounds good. I'm not sure why it works, but oh well...

=P
 
You must log in or register to reply here.
Back
Top