How to treat specific text as zero in a formula for finding LARGE numbers from cells

[masud_jahan]

Hi all !

I have a workbook where cells D12:H12 contain exam marks of students. I use a formula in cell I12 to sum the best 4 marks from cells D12:H12. But the problem arises when students were absent in exams and their marks for that exams are shown as *Absent* in the cells.

How can I treat the text *Absent* as 0 (zero marks) in the formula used in cell I12?

Thanks in advance.

[Tony Valko]

This will sum the 4 largest numbers in the range:

=SUM(LARGE(D12:H12,{1,2,3,4}))

Assumes there will be at least 4 numbers to sum.

[Tony Valko]

This array entered** version will account for when there are <4 numbers:

=SUM(IFERROR(LARGE(D12:H12,{1,2,3,4}),0))

If there are less than 4 numbers it will sum however many numbers are available.

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[JohnTopley]

As you only have 5 marks:

=SUM(D12:H12)-IF(COUNT(D12:H12)=5,MIN(D12:H12),0)

This ignores text and subtracts the MINIMUM value only if there are 5 marks

[masud_jahan]

This will sum the 4 largest numbers in the range:

=SUM(LARGE(D12:H12,{1,2,3,4}))

Assumes there will be at least 4 numbers to sum.

Thanks Tony !
But I'm facing problem when a student has 2 or more *Absent* value as marks in cells D12:H12.
How can I treat the text *Absent* as 0 (zero marks) in the formula used in cell I12?

[Tony Valko]

See post #3

[Sc0tt1e]

Can 't you create a column and add the marks into the column and anywhere there is an absent insert 0?

[JohnTopley]

Both formulas treat absent as 0 as SUM ignores text values (effectively zero).

[masud_jahan]

This array entered** version will account for when there are <4 numbers:

=SUM(IFERROR(LARGE(D12:H12,{1,2,3,4}),0))

If there are less than 4 numbers it will sum however many numbers are available.

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Thanks a lot Tony
It works great now.

[Tony Valko]

You're welcome. Thanks for the feedback!

[masud_jahan]

As you only have 5 marks:

=SUM(D12:H12)-IF(COUNT(D12:H12)=5,MIN(D12:H12),0)

This ignores text and subtracts the MINIMUM value only if there are 5 marks

Thanks John !
Your formula works successfully also