Faulty MIN and MAX function?

[Flick]

I've been monitoring the temperature in our office over the past week and am now wanting to produce a graph by working out the MIN and MAX for each day. However, both of these functions are failing to calculate the correct figures. It's probably something very simple, but I've not a clue why this is?

 

[Luke M]

the cells in range C17:C505 do not all have numbers. Some of them are numbers stored as text, and so when XL uses a number function like MIN/MAX/SUM, it interprets that text as 0, and thus is giving you the wrong answer.

I'd recommend converting the cells to all have actual numbers. Easiest way to do this is to:

1. In a blank cell, type a 1.
2. Copy that cell
3. Select cells with numbers stored as text
4. Paste Special - Values & Multiply

Done! Any cells with what looked like numbers will be converted to true number, and cells with regular text will be ignored.

 

[Deepak]

As Luke said there's no fault with the MIN/MAX IF, it's due to data type.

Check these.

{=MIN(IF($D$17:$D$505=F4,VALUE($C$17:$C$505)))}

{=MAX(IF($D$17:$D$505=F4,VALUE($C$17:$C$505)))}

 

[NARAYANK991]

Hi ,

Another alternative.

Narayan

 

[Flick]

Thanks a lot, it was driving me nuts! I could have sworn I changed the data type to numbers, but evidently not.