Multiple Science GCSE grades

[jh9940]

Hi
I am trying to solve a problem involving pupil data across multiple subjects - please see attached sample. Have tried to do it myself but keep getting the wrong outcome. Any help would be greatly appreciated - this is what I am trying to achieve:

- if there is a grade in BL (Science SA GCSE) then the corresponding points score (IS) will go into GZ (highest science score). Also, if there is a grade in BL then the corresponding points score from BM (Science BTEC - column IV) goes into GW (lowest science score)
- if there is a grade in BE (Science GCSE) then the corresponding points value goes into both GZ (highest) and GW (lowest)
- if there is no grade in either BL (Science SA GCSE) or BE (Science GCSE), then the corresponding points score from BI will go into GZ (highest) and the corresponding points score from BM (Science BTEC) goes into GW (lowest Science score)

I have figured it out for literacy and numeracy (GX/GY) but cannot work it out for Science

Thank you for any help
Much appreciated
Jon

[Limor_OP]

Hi,
Your grades are numbers or letters?

GW and GZ already have formulas in them, do you want to override it?

[jh9940]

Thank you very much for your reply.

There are grades in BL, BE and BM (U, G, F, E, D, C, B, A and A*). These get converted into point scores in columns e.g. column IS, IV. These point scores go into GW and GZ based on the rules above (if possible )

The formulas in GW and GZ can be overridden

Huge thanks

[Limor_OP]

Hi
- if there is a grade in BL (Science SA GCSE) then the corresponding points score (IS) will go into GZ (highest science score). Also, if there is a grade in BL then the corresponding points score from BM (Science BTEC - column IV) goes into GW (lowest science score)

I have to say your file if a little cumbersome. I see that column IS is related to BH, and not to BL.....
But let's say that IS is translating BI, then in GZ17 and down you can use this :

=IF(NOT(ISBLANK(BI17)),IS17,"")

In GW17 (assuming IV is a translation of BL:
=IF(NOT(ISBLANK(BL17)),IV17,"")

[jh9940]

Hi
Thank you. I have tried that but when I put a grade in for BL it returns nothing in the highest column (as the rules above)

[Limor_OP]

As I said - IV is translating BM in your file, and not like you wrote in your post.

In the bottom line you need to plant the cell number and its translation in the blow formula:
=IF(NOT(ISBLANK(Cell with a grade)),translation of that cell,"")

[Limor_OP]

Here is an example in the file:
GW19:
=IF(NOT(ISBLANK(BM19)),IV19,"")

[FDibbins]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)