maxima

charijit

Board Regular
Joined
Nov 27, 2003
Messages
89
I have

A1=2, A2=-3, A3=-55, A4=6, A5=9.

In A6 I wanted to put 1.1 times the maximum absolute value (55) with its sign (-55x1.1 = 60.5). For that I wrote

A6=LOOKUP(MAX(ABS(A1:A5)),ABS(A1:A5),SIGN(A1:A5))*ROUNDUP(1.1*MAX(ABS(A1:A5)),0).

The result of this formulae is #VALUE!. I wish to know my fault and also the method to achieve the desired result.

Wish you everyone MERRY CHRISTMAS.
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,402
Hi, and Merry Christmas to you too!

Try the following...

=INDEX(A1:A5,MATCH(MAX(ABS(A1:A5)),ABS(A1:A5),0))*1.1

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
charijit said:
I have

A1=2, A2=-3, A3=-55, A4=6, A5=9.

In A6 I wanted to put 1.1 times the maximum absolute value (55) with its sign (-55x1.1 = 60.5). ...

Do you want to see 60.5 or -60.5 as result?
 

charijit

Board Regular
Joined
Nov 27, 2003
Messages
89
Aladin,
Sorry for the mistake. I wish to see -55*1.1 = -60.5 rounded up to -61.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

charijit said:
Aladin,
Sorry for the mistake. I wish to see -55*1.1 = -60.5 rounded up to -61.

Wrap the formula expression Domenic suggested inside ROUND or ROUNDUP with decimal places set to 0.
 

ssywak

New Member
Joined
May 25, 2006
Messages
7
w00t!

Man!

This is just the thing I was looking for!

Thanks, fellas!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,328
Messages
5,571,564
Members
412,405
Latest member
DutchMonkey
Top