Find the highest result in a range of formulas

[Nigeljones]

I have a range of cells that have formulas in them that give a number as a result. I want to find the highest number. When I use MAX, the end result is always 0 as it is looking at the formula and not the result.

 

[Nigeljones]

I need to get the highest number in the following cells B:5 to B:10, F:5 to F:10 and J:5 to J:10. If the answer i say 14 it tells me that their are 14 acts from which I can time the section. I have a formula in there which is COUNT which works well but when there are duos or trios the timing is out because 1 act can be 3 people.

 

[Nigeljones]

When I put MAX(B5:B10) into my excel sheet it only produces 0 as the result. What could cause this

 

[Michael M]

Are you putting it on the same sheet ?
What cell is it going in ?

 

[alz]

When I put MAX(B5:B10) into my excel sheet it only produces 0 as the result. What could cause this

I assume Max is return 0 because there is no number in B5:B10 (only text which appear as number) Check with istext

 

[Nigeljones]

I have tried putting the max function directly under the range , i have formatted the cells as numbers still only getting 0. You seemed to get a result in the mini sheet

 

[alz]

I have tried putting the max function directly under the range , i have formatted the cells as numbers still only getting 0. You seemed to get a result in the mini sheet

Had you checked with istext ? (text which appear as number can not be converted into a number by changing formating)

Copy B5:B10 Paste as value in blank cell then check with istext

 

[Dave Patton]

Columns M and N were identical
Then I completed the following:
- Copied a blank cell Control C
- Selected the range N4:N8
- Paste Paste Special Add
The Formula now yields the correct Max.

T202101b.xlsm
	M	N
1
2
3	Text
4	20000	20,000.00
5	5000	5,000.00
6	50	50.00
7	20	20.00
8	30	30.00
9
10	0	20,000.00
6b

Cell Formulas
Range	Formula
M10:N10	M10	=MAX(M4:M9)

 

[Nigeljones]

It appears that the cells are all formatted as text

 

[alz]

So you can try it either way .changing Text to number in original Data
Before you try this make a copy of workbook.
Type 1 in any blank cell => Copy => Select the range which you want to change => paste special => value and multiply => Ok


or
Make sure Clear the cells first in where you want to put formula then (including formating )
=AGGREGATE(14,6,((B5:B10)*1),1)
=AGGREGATE(14,6,((F5:f10)*1),1)
=AGGREGATE(14,6,((J5:J10)*1),1)

 

[Dave Patton]

You can also use Data Text to Column then last choice is General
It will convert Text to Values