This bugger doesn't work

apurk45

Board Regular
Joined
Oct 23, 2002
Messages
219
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
:oops:
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Board Regular
Joined
Oct 23, 2002
Messages
219
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

Board Regular
Joined
Oct 23, 2002
Messages
219

ADVERTISEMENT

Aladin

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

I putting my formula into H9.

Apurk45
 

Ahnold

Well-known Member
Joined
Feb 20, 2004
Messages
636
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

apurk45 said:
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

Board Regular
Joined
Oct 23, 2002
Messages
219
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
apurk45 said:
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

Board Regular
Joined
Oct 23, 2002
Messages
219
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,885
Messages
5,766,936
Members
425,388
Latest member
Cave_Johnson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top