Login

Welcome, Guest. Please login or register.

April 22, 2024, 12:44:07 am

Author Topic: Excel Help  (Read 5148 times)  Share 

0 Members and 1 Guest are viewing this topic.

Srd2000

  • Trendsetter
  • **
  • Posts: 140
  • Respect: +16
Excel Help
« on: May 24, 2018, 11:54:24 am »
0
Hey, does anyone know how to associate a word with a number in Excel? What I mean is, if you have a number, say 90-100, it spits out an A in a different cell. If the number is 80-89, it spits out a B.... Does that make sense? Is there an easy way to do it?

Thank you
2017 - Maths Methods (CAS), Chemistry, Physics

2018 - Specialist Maths, English, Japanese (SL)

vox nihili

  • National Moderator
  • Great Wonder of ATAR Notes
  • *****
  • Posts: 5343
  • Respect: +1447
Re: Excel Help
« Reply #1 on: May 24, 2018, 01:03:06 pm »
+3
Hey, does anyone know how to associate a word with a number in Excel? What I mean is, if you have a number, say 90-100, it spits out an A in a different cell. If the number is 80-89, it spits out a B.... Does that make sense? Is there an easy way to do it?

Thank you

The IF function is probably best here.

Example of how you could do this with yours:


IF(A1>89,"A",IF(A1>79,"B",IF(A1>69,"C")))


Where A1 is the cell with the grade in it. The cell that you copy the formula into will be the one that records the A/B/C. You can just drag the formula and it will go from A1 to A2 to A3 automatically.

Obviously you can follow the same logic to change the numbers and the cell numbers too. 
2013-15: BBiomed (Biochemistry and Molecular Biology), UniMelb
2016-20: MD, UniMelb
2019-20: MPH, UniMelb
2021-: GDipBiostat, USyd

www

  • Trendsetter
  • **
  • Posts: 196
  • Respect: +86
Re: Excel Help
« Reply #2 on: May 24, 2018, 01:58:41 pm »
+1
The IF function is probably best here.

Example of how you could do this with yours:

IF(A1>89,"A",IF(A1>79,"B",IF(A1>69,"C")))

You can use the IFS function to make this a little cleaner!

=IFS(A1<80,"C",A1<90,"B",A1<100,"A")

There is a way to do it with VLOOKUP and a separate grade table too, but if the range criteria for each grade doesn't change, IFS should be sufficient.
« Last Edit: May 24, 2018, 02:03:14 pm by www »
2017~2020 (Monash) | BA, BA(Hons)Psy
2021~ | job! - AN hiatus, it's been fun here (:

vox nihili

  • National Moderator
  • Great Wonder of ATAR Notes
  • *****
  • Posts: 5343
  • Respect: +1447
Re: Excel Help
« Reply #3 on: May 24, 2018, 06:21:44 pm »
+1
You can use the IFS function to make this a little cleaner!

=IFS(A1<80,"C",A1<90,"B",A1<100,"A")

There is a way to do it with VLOOKUP and a separate grade table too, but if the range criteria for each grade doesn't change, IFS should be sufficient.

Groundbreaking!

Didn't realise you could do that; thought mine looked really clunky! Thank you :)
2013-15: BBiomed (Biochemistry and Molecular Biology), UniMelb
2016-20: MD, UniMelb
2019-20: MPH, UniMelb
2021-: GDipBiostat, USyd

Srd2000

  • Trendsetter
  • **
  • Posts: 140
  • Respect: +16
Re: Excel Help
« Reply #4 on: May 24, 2018, 10:18:13 pm »
0
Sweeto, thanks for that. Slight problem, using the IFS function, I get an #NAME? error. Any other suggestions?
What is this VLOOKUP you speak of???
2017 - Maths Methods (CAS), Chemistry, Physics

2018 - Specialist Maths, English, Japanese (SL)

jamonwindeyer

  • Honorary Moderator
  • Great Wonder of ATAR Notes
  • *******
  • Posts: 10150
  • The lurker from the north.
  • Respect: +3108
Re: Excel Help
« Reply #5 on: May 24, 2018, 10:33:38 pm »
0
Sweeto, thanks for that. Slight problem, using the IFS function, I get an #NAME? error. Any other suggestions?
What is this VLOOKUP you speak of???

Can you copy paste your formula? #NAME means a syntax error, maybe a comma or a quote out of place ;D

VLOOKUP is a nifty little function. You give it a search value, and it looks for it in a table column. When it finds it, it returns the value in another column for that same row. See here.

I think the best thing for you to do will depend on how many cases you need. Based on your numbers, I reckon you are doing grades? So it is just five cases - An IFS function is probably what I'd go with there. VLOOKUP is good if you plan for those thresholds (90-100 for A, 80-89 for B, etc) to change. Then you can just change the table :)

Srd2000

  • Trendsetter
  • **
  • Posts: 140
  • Respect: +16
Re: Excel Help
« Reply #6 on: May 25, 2018, 12:16:08 am »
+1
Yeah, I tried copy and paste, but nothin' changed. Still errored. No worries cause I'm now using the VLOOKUP and it's working a charm ;) Thanks
2017 - Maths Methods (CAS), Chemistry, Physics

2018 - Specialist Maths, English, Japanese (SL)

jamonwindeyer

  • Honorary Moderator
  • Great Wonder of ATAR Notes
  • *******
  • Posts: 10150
  • The lurker from the north.
  • Respect: +3108
Re: Excel Help
« Reply #7 on: May 25, 2018, 09:18:20 pm »
0
Yeah, I tried copy and paste, but nothin' changed. Still errored. No worries cause I'm now using the VLOOKUP and it's working a charm ;) Thanks

Sorry, I meant like copy/paste it HERE for us to look at, but easy as!! VLOOKUP is super handy to know :)