I have a very weird issue with my Numbers for MacOS. Whenever I hold and drag down in my VLOOKUP column to apply the formula to all my students, VLOOKUP gradually omits more and more data from the range. It looks like this - as I go lower in the lookup column, the less source data is being taken.
The lower I go the less data from the right columns is being used (marked pink)
Since there is a small number of possible letter grades you may find it simpler and less cluttered to use a formula rather than a separate lookup table.
Entered as:
=IFS(A2>=90%,"A",A2>=70%,"B",A2>=20%,"D")
You may need to change this slightly to reflect your actual grading system. I just took a guess based on your screenshot.
If you actually fail some students then you could extend this to something like this:
=IFS(A2>=90%,"A",A2>=70%,"B",A2>=20%,"D",A2>=0%,"F")
More on IFS here.
Replace the , in the formulas with ; if your region uses , as a decimal separator.
SG
Since there is a small number of possible letter grades you may find it simpler and less cluttered to use a formula rather than a separate lookup table.
Entered as:
=IFS(A2>=90%,"A",A2>=70%,"B",A2>=20%,"D")
You may need to change this slightly to reflect your actual grading system. I just took a guess based on your screenshot.
If you actually fail some students then you could extend this to something like this:
=IFS(A2>=90%,"A",A2>=70%,"B",A2>=20%,"D",A2>=0%,"F")
More on IFS here.
Replace the , in the formulas with ; if your region uses , as a decimal separator.
SG
Hi bartosztomasz,
Does your formula has any $ signs in this section?
It should have $ signs in front of the letters and the numbers, this will preserve the position in the table when you drag the formula down.
Based on your region the , or the ; will be used to separate the different sections of a formula. If you write one thousand as 1,000.00 then the , is used as your formula separator. If you write one thousand as 1.000,00 then the ; is used as your formula separator.
Hope this will solve your question, please let me know if this worked for you or if something in unclear.
Regards Ralf
Please check XLOOKUP, this will give you more options and can reduce the size of your "lookup table"
With XLOOKUP you can not only search for an exact match, that can make the Lookup Table shorter.
If the student has 97% the "exact or next largest" would find 100%, therefore you don't need a row for 100, 99, 98, ...
Ralf
True, I have to check that function since it's extremely awkward to have the enormous list of 100 separate points. Range from-to would be much better. I didn't know about this function. I'll check it soon. Thank you.
Indeed there were no $ symbols by default and I didn't pay attention to it. Now it is working flawlessly. Thank you :)
VLOOKUP drops some rows and doesn't populate.
