Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
I wonder if someone can help... I have a row of grades a-d, and some blank cells that I want it to ignore, which I need to be able to report an average of as a number where a=1, b=2, c=3 and d=4. It's important that it ignores blanks rather than assumes blank=0 as that really affects the average. i.e. if I had a,a,a,c then it would report 1.50.
I am currently doing this by running a mirror sheet which uses vlookup to convert each letter to a number, and then =average to work out what I need. There must be a way of doing this in one cell without the mirror sheet, but I can't work out what that is. Anyone....?
I don't know a vast amount of Excel so I don't know if this will help, but you can do it computationally.
You can use CODE to convert it to ASCII, which is 65 for A, 66 for B and so forth. Subtract 64 from this and you've got A=1 etc. (Lower case starts with a=97).
Nested if statement?
=IF(A1="a",4,IF(A1="b",3,IF(A1="c",2,IF(A1="d",1,""))))
Nested if statement?
I tend to use SWITCH instead of nested if -
=SWITCH(A1,"a",1,"b",2,"c",3,"d",4,"")
RM.
Easier way to convert a letter to a number
=COLUMN(INDIRECT(A1&1))
but I haven't thought of an easy one formula way to do what the OP wants without a rather long formula
I tend to use SWITCH instead of nested if –
=SWITCH(A1,”a”,1,”b”,2,”c”,3,”d”,4,””)
Nice - Elegant.
Here you go, OP.
=SUMPRODUCT(({"a";"b";"c";"d"}=A1:J1)*{1;2;3;4})/COUNTA(A1:J1)
Change "A1:J1" to whatever it needs to be. If you get stuck, shout.
Nice solution Rob Hilton
Rob - thank you very much for your time, that doesn't seem to work - I get a "did you mean to enter a formula?" error.
Any ideas? I copy/pasted it in and changed both cell ranges to the ones I need. Tried entering as array formula and normal formula - no difference. I thought with an array formula that you didn't enter the curly brackets and just press ctrl+shift+enter?
Rob's formula works a treat, I gave it a try but.......you may need to replace the copy and pasted speech marks with ones you type in yourself
RB is probly right.
Sumproduct() is an array function, so no need to ctrl+shift+enter. You can use arrays as arguments in other "regular" functions too. Very powerful things can be done :).
Amazing - thank you. Replacing the quotation marks fixed it.