Obtaining the minimum of values which have been generated by a for

[Caithness Girl]

I have a list of values that have been generated by a formula (IMSUB). I want
to be able to show the minimum of these values in a cell but using the MIN
and MINA functions, these just come back with 0. There isn't a 0 in the list
of numbers. Please help - I am a bit of a "noddy" with Excel
unfortunately......

 

[Luke M]

The problem is that the IMSUB function returns text values (and text has a
numerical value of 0). Since you are dealing with imaginary numbers, it is a
bit harder for XL to clearly define what is the "minimum". A possibility
would be to use either the IMABS function to convert the text into a single
value. Then MIN function could then be used to find that. If you then wanted
to know the original complex number, could combine with a MATCH and INDEX...

Let's say that A1:A5 is your IMSUB formulas. B1:B5 contains the IMABS
formulas. To find the minimum value in A1:A5 based on the values in B1:B5,
you could do this:

=INDEX(A1:A5,MATCH(MIN(B1:B5),B1:B5,0))

 

[JLatham]

This is because IMSUB() return a text result, not a number. You'll need a
'helper' column to find the minimum.
Let's say you have an IMSUB() formula in cell E13 that displays 8+i and that
you are going to use column F for your helper column, then in F13 you can put
this formula:
=VALUE(LEFT(E13,FIND("+",E13)-1))
and that will give you a real number; 8
continue that formula down the column for all of your IMSUB() formulas, then
do a MIN() on the entries in column F.

Lets say your MIN() formula looks like =MIN(F2:F44) but you want it to show
you the result in the "n+i" format, then change it to
=MIN(F2:F44) & "+i"

Hope this helps.

 

[Caithness Girl]

I did manage to convert the text into numbers using the VALUE function -
thanks !!! So some of the Excel function return text rather than numbers even
when it is only numbers that have been used in the functions ? When
subtracting numbers from different cells, shoudl I have used a different
function to IMSUB ?? Thanks again !!

 

[Luke M]

Correct, some functions in XL return text even when input is a number. For
instance, the LEFT/RIGHT functions always return text, even when input is a
number such as:
=LEFT(100,1)
returns the text "1".

I assumed you were using IMSUB because you actually were dealing with
imaginary numbers, in which case you had no alternative. If you are just
dealing with real numbers, you can skip the function altogether and just type:
=A2-A1
or variations, such as:
=SUM(A1:A10-B1:B10)

 

[Luke M]

Apologies, ignore that last formula. My brain had temporarily taken a leave
of absence...

 

[Caithness Girl]

THANKS - As I said I am a bit of a "noddy" with Excel but certianly am
finishing today wiser than I started this morning !!!

Cheers,