Max points given is 10, even if result is greater

[vill]

Hi guys, would you know what could be added into existing formula to generate a result, as per Excel and screenshots below? Thanking in advance!

NOTE: result 20 in example needs be 10 as the max.

https://www.excelforum.com/attachmen...1&d=1533923515 [EXCEL]

2018-08-10_1949.png

[Tsjallie]

I guess this will do the trick

Please Login or Register  to view this content.

[vill]

That was it, Tsjallie! Thank you sir, much appreciated. Have a nice day

[Tsjallie]

You're welcome :-)

[jtakw]

Hi,

Above formula in Post #2 will Error out if D3:D4 does not contain any Text (Blank or numbers), the MIN function should be Inside the IFERROR like this:

Please Login or Register  to view this content.

[Tsjallie]

You're sharp
But

Please Login or Register  to view this content.

will render a blank cell.
Better to have 0 as the alternative value of the IFERROR:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[jtakw]

The "" for IFERROR was OP's choice, and was used in your post #2 also.

While in some instances, one may Not want to use a "" as result, in my opinion, this one works well using "" rather than 0 (zero).

The IFERROR handles the #DIV/0! error when there's no value in Column D

However, when there's no "y" in Column E, but there Is Text in Column D, the formula will produce a 0 (zero) result (i.e. 0/1)

Therefore, if we use 0 for the IFERROR, Both #DIV/0! and a legitimate calculated result of the formula will produce a 0 (zero), and the user will not be able to distinguish the difference just looking at the result.