This bugger doesn't work

[apurk45]

Hi
Can any of you tell what is wrong with my formula?

=IF(AND(K8>0,M8="IJK",MAX(ABS(F8),ABS(F9),ABS(F10))=ABS(F8)),G8,IF(MAX(ABS(F8),ABS(F9),ABS(F10))=ABS(F9),G9,IF(MAX(ABS(F8),ABS(F9),ABS(F10))=ABS(F10),G10,"")))

My cell K8=0 and for ungodly reason my formula does not recognize it.
Also if you have any suggestion how to make this formula shorter would be appreciated.

Thanks
Apurk45

 

[onlyadrafter]

Hello,

Does K8>=0 work?

 

[Aladin Akyurek]

Try...

=IF(N(K8)*(M8="IJK")*ISNUMBER(MATCH(MAX(ABS(F8:F10)),ABS(F8:F10),0)),INDEX(G8:G10,MATCH(MAX(ABS(F8:F10)),ABS(F8:F10),0)),"")

which must be confirmed with control+shift+enter instead of just with enter.

In which cell do you put this formula?

 

[apurk45]

No does not work.

When K8=0 I should get "" in formula cell otherwise value from G8, G9 or G10.

Currenty I am getting the value from G cells even though my K8=0.

Apurk45

 

[apurk45]

Aladin

Did not work - same problem. I used Crl+Shf+Enter.

I putting my formula into H9.

Apurk45

 

[Ahnold]

Try this:
=IF(AND(K8>0,M8="IJK",MAX(ABS(F8),ABS(F9),ABS(F10))=ABS(F8)),G8,IF(AND(K8>0,MAX(ABS(F8),ABS(F9),ABS(F10))=ABS(F9)),G9,IF(AND(K8>0,MAX(ABS(F8),ABS(F9),ABS(F10))=ABS(F10)),G10,"")))

 

[Aladin Akyurek]

Aladin

Did not work - same problem. I used Crl+Shf+Enter.

I putting my formula into H9.

Apurk45

Did not work means what? An error value?

If you like, post the data in the area F8:G10. And explain in words what must be computed?

 

[apurk45]

Aladin
My apologies your formula works great. I fat fingered some incorrect cell #.

Ahnold
Your formula works as well.


Thank you very much Guys.

PS.
Even though your formula works perfect – I am lost as to understanding how it works. Would you mind explaining logic of your combabulation?


Thanks
Apurk45

 

[Aladin Akyurek]

Aladin
My apologies your formula works great. I fat fingered some incorrect cell #.

...
Even though your formula works perfect – I am lost as to understanding how it works. Would you mind explaining logic of your combabulation?...

We can break this up in 2 cells to gain more efficiency...

Instead of the following in H9...

=IF(N(K8)*(M8="IJK")*ISNUMBER(MATCH(MAX(ABS(F8:F10)),ABS(F8:F10),0)),INDEX(G8:G10,MATCH(MAX(ABS(F8:F10)),ABS(F8:F10),0)),"")

in I9 control+shift+enter:

=MATCH(MAX(ABS(F8:F10)),ABS(F8:F10),0)

then in H9 just enter:

=IF(N(K8)*(M8="IJK")*ISNUMBER(I9),INDEX(G8:G10,I9),"")

Now explaining my "confabulation" becomes much easier:

The formula in I9, if successful, establishes the position of the (first instance of the) MAX value in F8:F10. The presence of ABS applied to a multicell range requires a formula that operates on arrays...

The IF formula in H9 has a condition (actually: subconditions strung together with *, which behaves like boolean AND)...

N(K8) gives 0 if K8 houses either a real 0 or text like "", otherwise returns the non-zero number that K8 houses.

The M8="IJK" bit either returns TRUE or FALSE.

The ISNUMBER(I9) returns TRUE if the MATCH formula in I9 returns a position indicating (non-zero) number, otherwise FALSE when the MATCH formula returns #N/A.

So (a) 0 or Non-Zero multiplied with (b) TRUE or FALSE multipled with (c) TRUE or FALSE is bound to return either a 0 or a non-zero number. Excel interprets a 0 as FALSE and a non-zero number as TRUE. A non-zero number as result of IF's condition leads to the evaluation of the

INDEX(G8:G10,I9)

which fetches the value from G8:G10 at pos I9. A 0 as result of IF's condition, leads to "".

 

[apurk45]

Re: This bugger doesn't work [b](Solved)[/b]

Aladin

Thank you very much for your time. You are very good and VERY helpful.

Apurk45