Forum Discussion
CORRECT AN EXCEL FUNCTION
Hi Sergei, thanks for your interest.
Actually, i am trying to build a scoring where :
- S8=Major; "1" or
- S8=Valuable;"2" or
- S8=Average;"3" or
- S8=Weak;"2" or
- S8=Null;"1"
It seems there are too many conditions for Excel capacity
What do you think ?
Thomas,
To clarify a bit - if in S8 one of texts ("Major", etc.) you'd like to return by formula into some cell another text ("1", etc.)?
And what to return if S8 doesn't match any from the list?
Sorry Sergei, i am french and don't understand well what you mean.
In S8, it is a drop-down band from "Major" to "Null".
According to the choice made, the cell X8 will show a rating from 5 (best) to 1 (worst).
Dear all, i thank you for your help.
Finally, i have found the exact formula which i a sharing with you as follows :
=SI(S8="Majeur";5;SI(S8="Fort";4;SI(S8="Moyen";3;SI(S8="Faible";2;SI(S8="Nul";1)))))
Many thanks to you all
Thomas,
That's great you found the solution yourself. Nested IF works fine.
As the comment. As always in Excel you may achieve the result by several ways. The only minus of your formula is maintainability. If one day you decide to change "Nul" on "Extremely Low" or, as in UK, grades will be changed from alphabetical A-G on numerical 1-9, you have to change all your formulas.
Better to keep the list with scores somewhere in Excel sheet and connect all your formulas to this list. If any changes you correct only the list, in one place, not all formulas.
To S8 and other cells you may define data validation based on this list like this
assuming your list is in A2:A6 (and related scores are in B2:B6). Could at any place in your workbook.
To calculate related scores in X8, etc, the formula could be like
=IFERROR(INDEX($B$2:$B$6,MATCH(S8,$A$2:$A$6,0)),0)
MATCH finds the position of the S8 in the list and INDEX returns related score.
Please see attached file.