formula to work out marks needed please !

[ajr45]

At college we are designing a system which will allow teachers to enter marks into a sheet for each unit. At the side of this table it displays how many marks the student is from getting to the next mark.

=IF(W3=0,"",IF(W3>Boundaries!A33,"-",Boundaries!$A$33-'Unit 2'!W3))

this is what i have put in but it wont work, it is still displaying a number even though the mark has been met...


if the total mark (W3) is 0 then i need it to leave the cell blank, if the maximum unit mark has been reached the cell is left with a "-", if the maximum mark has not been met then it needs to show how many marks the student needs to achieve that mark.

Sorry if i havent worded it well

hope someone can help !
thanks

 

[Trevor G]

Welcome to the forum.

Have you thought about creating a table which you can use with a VLookup and a Validation so the Teacher can select from a drop down, it then populates the mark and you only need some extra details like how many points is needed to get the required mark. Add an if statement to ensure something has been selected in the cell and the Vlookup will activate and give you the correct information. Show no data if no value is selected.