Excel formula help please..

[Neill_J]

Hi

I am trying to work out a formula to use in an excel sheet I am working on, but am struggling to find one to do what I want:

Basically I want to check a range of cells, and if there are numbers in this range - return the min and max of these (easy to do using the min & max functions)
However if there are not any numbers in the range then I want to return a text value of "None" instead of a value of 0 (which it currently does)

E.G.

If I have cell values:

A1, B1, C1, D1
1, 2, 3, 4

I want to check these to see what the min and max values are

Cell E1 (Min figure =MIN(A11) = 1
Cell F1 (Max figure =MAX(A11) = 4

Fine so far, but if the values are instead:

A1, B1, C1, D1
N/A, DNS, DNF, N/A

This returns a value of 0 in E1 & F1
What I want in this case is to be able to return a text value instead (like "none") (I.e. when there are no numbers in the range - return a text value - to do this though you need to test what is in the range)

I tried to get around this by using an IF test

=IF(A11="N/A","None",IF(A11="DNF","None",IF(A11="DNS","None",MIN(A11))))
=IF(A11="N/A","None",IF(A11="DNF","None",IF(A11="DNS","None",MAX(A11))))

This is not quite right as it is testing to see if the whole of the range of A11 is equal to N/A, DNF or DNS rather than each individually (this returns a #VALUE! Response)
Any ideas what I need to do to sort this?

BTW - If I have values:

A1, B1, C1, D1
1, DNS, DNF, N/A

This works using the Min and Max functions (both return 1), however it falls over when I have no numbers in the range -so any solution must cover 3 scenarios:

All numbers in range
Some numbers in range + some of one or more of these - DNS, DNF, N/A
No numbers in the range, mix of one or more of these - DNS, DNF, N/A

Cheers - Neill

 

[mark66]

So in your last examle, what do you want returned?

A1, B1, C1, D1
1, DNS, DNF, N/A

Is this what your looking for? =IF(MIN(A2 : D2) = 0,"NONE",MIN(A2 : D2))

This will return the lowest number, when all or some cells are numbers and will return NONE if none of the cells are numbers.

By the way, how do you stop colon capital D not becoming a ? I think I remember something when I looked at html but I can't remember?

 

[Neill_J]

Hi

Not sure if I made myself clear...
I am looking for a formula which will check a range (say A1 to D1) to see if they contain numbers - if they do, I have 2 cells (E1 and F1) which are to be used to show the min and max numbers found in the range.

If there are a mix of numbers and text in the cells checked (A1 to D1) then it should still return the min and max numbers found

If however the cells A1 to D1 contain only text then the results for E1 & F1 should be "None"
The text entries in cells A1 to D1 will be one of the following types:

N/A, DNS, DNF,

I shall give your suggestion a try and see if it does what I need - thanks

Um - I think I used an option on the reply page when posting something like - Disable smilies in text?
That should do it I think

Cheers - Neill

So in your last examle, what do you want returned?

A1, B1, C1, D1
1, DNS, DNF, N/A

Is this what your looking for? =IF(MIN(A2 : D2) = 0,"NONE",MIN(A2 : D2))

This will return the lowest number, when all or some cells are numbers and will return NONE if none of the cells are numbers.

By the way, how do you stop colon capital D not becoming a ? I think I remember something when I looked at html but I can't remember?

 

[mark66]

...If there are a mix of numbers and text in the cells checked (A1 to D1) then it should still return the min and max numbers found...

In that case that formula works just fine. You were nearly there, it just needed a slight tweak

Edit: Just noticed my example uses row 2, should be 1 for you example!

 

[Neill_J]

Hi

Just checked it out - works just as I needed thanks a lot

I wasnt thinking latteraly enough!
Sometimes with excel you miss the easy solution - looking to solve it more using more complex methods

Cheers again - Neill

In that case that formula works just fine. You were nearly there, it just needed a slight tweak

Edit: Just noticed my example uses row 2, should be 1 for you example!

 

[mark66]

No worries, glad it has done the trick