Forum Discussion
logical function
Thank you very much for your response. Actually, I want a formula that get me the same values in the score column. Those values in the score column are constant base on the respondent response under each of the disability domains.
I don't understand. The formula I gave takes the responses from the 6 disability domains and produces the Yes/No 'Score' and in every case in your table my formula produced the SAME Score as you had listed. Your Score in column H and my formula in column I. I thought I attached the sample file but I will try again here.
If you feel my formula is not accurate, please let me know why or what other considerations are needed. I can only go on the information you provide.
This work perfectly! I appreciate this very much. My concern is the All answers and count in the K and L cell. is it that anytime in the future I want to build this form I should also do same as in K and L or there is any other way of not referencing K and L but still get the required result?
You can 'hard code' those values into the formula, or you could create a 'name' variable with them instead. Here it is 'hard coded':
=IFERROR(IF(AVERAGE(XLOOKUP(UPPER(B3:G3),{"NO","SOME","A LOT","UNABLE","MISSING"},{0,0,1,1,"M"})),"Yes","No"),"Missing")