How do I create a formula for varied scores? (For a teaching assessment spreadsheet)

[Colin.D]

Hi all,

I hope that someone might be able to help me create a formula for a school report that includes a range of assessment grades. I have no idea how to create a formula in a cell that calculates a final score for varied grade boundaries. Hopefully the link below helps show the Excel document and missing "Final Subject Grade 1-7"


https://skydrive.live.com/edit.aspx?...=cpe&app=Excel

Br,

Colin

[JungleJme]

Hi,

Could you please attach the file via the forum's attachment tool? (Go Advanced > Attach)

Also, ensure that the file is a "sample" that shows the principle but doesn't contain live data such as names, addresses etc.... Also its helpful to show what result you want in the sample, even if you have to manualyl type it...

[Colin.D]

Hi,

Thanks for the reply. I deleted the sky drive link and took the school name from the Excel sheet. I replaced it with another link. If you scroll down, you will see a list of grade boundaries from 1-7 for different subjects. The number 1,or 2, or 3, or 4, or 5, or 6, or 7 should appear in the cells in the "S" column.

https://skydrive.live.com/edit.aspx?...=cpe&app=Excel

I also added a copy using the advanced options you described.

Br,

Colin

[JungleJme]

Hi,

There are a couple of solutions, the first was to seperate your grade bands into min maxs then use a series of nested ifs to basically check the final mark against each band. However, this would have required several nested IF statements which excel 2003 may not like.

So, instead i created a grid/index of marks. Where each column represented a faculty.

I then named each column in the "Name Manager", this makes the formula much neater and simple! You can hide the columns with my index in and they will sit there happily calculating away!

Enjoy,

J

[Colin.D]

Thank you so much JungleJme or "J".

I am very impressed! I'm very new to this site and hope to give you some credit for solving my Excel problem.

Br,

Colin