Function to return MAX value for a range

[jwright650]

I can't get my brain around which function to use use to return the largest number in a range of cells(E2:CA2) and show the result in CG2.

I thought about IF and MAX functions, but I'm unsure if I'm even using them correctly or not. I'm tired of wasting hours on this simple problem and need some help.

I've attached a worksheet so you can see what I'm up against.

The employees earn a cash bonus for every 30 days of working safely or not having a safety violation ($25 for 30 days, $30 for 60 days, $35 for 90 days, and $40max for 120 days or more)

BTW, The employee names and clock numbers are linked to another workbook so if they don't show up for you that is why.

[NBVC]

Why not just?

=MAX(E2:CA2)

Actually, it looks like the formulas in E:CA are text results.... the formulas should not have numbers in quotes. i.e. =IF($**$2-D2>=30,"25","0") should be =IF($**$2-D2>=30,25,0)

You should fix them...

otherwise you need to coerce them to numbers with: =MAX(E2:CA2+0) confirmed with CTRL+SHIFT+ENTER not just ENTER

or =SUMPRODUCT(MAX(E2:CA2+0))

and copied down.

[jwright650]

=MAX(E2:CA2)

I tried that but it returns $0 even when the employee has $40 showing for several months....I've tried so many combinations that I've become very confused...thus I needed you guys to help me out.

[jwright650]

Actually, it looks like the formulas in E:CA are text results.... the formulas should not have numbers in quotes. i.e. =IF($**$2-D2>=30,"25","0") should be =IF($**$2-D2>=30,25,0)

Ahhhh, I need to look at that for a minute....this was created many, many years ago and now i'm trying to get back in there and fix a few things.


edit: I'm going to fix the values and then try the MAX formula again to see how that works...<heads off to repair worksheet>

[jwright650]

Also....Sheet2 is just a report for the secretary so she can make sure the employee gets the correct $ in their envelope. I was trying to automate that for her by referencing the CG column on Sheet1

[TMS]

It might be a stupid question, but what is "MAXA"?

Regards

[jwright650]

It might be a stupid question, but what is "MAXA"?

Regards

Not a silly question....

MAXA


See Also
Returns the largest value in a list of arguments. Text and logical values such as TRUE and FALSE are compared as well as numbers.
MAXA is similar to MINA. For more information, see the examples for MINA.
Syntax
MAXA(value1,value2,...)
Value1, value2, ... are 1 to 30 values for which you want to find the largest value.

[NBVC]

See my ammendment above.

[jwright650]

Life is all good again...all solved....LOL

MAX formula works correctly now that the data is the correct type.