formula help ?

[coltheplumb]

Hi ho you lot ok i have this formula that looks at 5 cells( 1 unit each cell) and then gets a result from the vlookup’s. now the students are going to be given a choice of completing either 3, 4 or 5 units .(3,4 or 5 cells)any variation.
I now need to do the same formula and get a result from whatever cells/units they choose. i know i will need to do more vlookups and i am happy to add an extra cell to show how many cells/units they have chosen to do , now if your still with me do you have any idea how i could do this ??? i have added the the formula below.

=IFERROR(VLOOKUP(VLOOKUP(AE7,$EK$3:$EM$5,2)+VLOOKUP(AF7,$EK$3:$EM$5,2)+VLOOKUP(AG7,$EK$3:$EM$5,2)+VLOOKUP(AH7,$EK$3:$EM$5,2)+VLOOKUP(AI7,$EK$3:$EM$5,2),$DO$4:$DP$6,2),"")
all help appriciated
Colin X

 

[Jonmo1]

In the part where you're adding the 5 vlookups together...

Will there be only 1 match for each vlookup?

I mean, will the value in AE7 only appear ONCE (or not at all) in EK3:EK5 ?

If so, this part
VLOOKUP(AE7,$EK$3:$EM$5,2)+VLOOKUP(AF7,$EK$3:$EM$5,2)+VLOOKUP(AG7,$EK$3:$EM$5,2)+VLOOKUP(AH7,$EK$3:$EM$ 5,2)+VLOOKUP(AI7,$EK$3:$EM$5,2)

Can be reduced to

=SUMPRODUCT(SUMIF($EK$3:$EK$5,AE7:AI7,$EM$3:$EM$5))

And AE7:AI7 can be any size range (blanks will be ignored).
So you can enter any number of values in the range.


And you no longer need the Iferror, because sumproduct will return 0 instead of error when there is no match.


Hope that's helpfull.

 

[coltheplumb]

Hi jonmo1
Yes that does what i asked thanx, but now i have put it in and thinking about it I will need to have a cell that will require the amount of units/cells being taken added, the reason for this is i have set up vlookups to cover 2 unit outcome, 3 unit outcome, 4 unit outcome, etc in other parts of my tracking sheet so when the amount is entered 2,3,4 etc it can relate to the vlookup that covers that number of cells/units.
So any other ideas now
Colin X

 

[Jonmo1]

You could probably use COUNT to determine how many enteries there are..

=COUNT(AE7:AI7)

That will tell you how many numerical entries there are..

I take it your vlookup will need to vary the colindex # right?
From 2 to 3 to 4 etc.. based on how many entries there are?

Can you give the specific details of that?

 

[coltheplumb]

Well each vlookup consists of a percentage amount of each unit once the percentage amount adds up to 100 then it looks at another vlookup(the last part of my original formula) to change the amount into a letter , either P, M or D depending on what parts of each unit they get.
I was thinking of doing somthing like if(AD7=3,then look at the vlookup for 3 unit pass marks,,,
If(AD7=4, then vlookup for 4 unit pass mark etc?
Not sure if that is clear... sorry
Colin X

 

[Jonmo1]

So AD7 would be the cell telling you how many entries there are? using COUNT or something?

Then

if(AD7=3,then look at the vlookup for 3 unit pass marks,,,
If(AD7=4, then vlookup for 4 unit pass mark etc?

What exactly is the vlookup for 3, and the vlookup for 4 etc...

 

[coltheplumb]

ok i shall try and explain short way... students have to do 22 different tasks, each task is split into units, some have 2 units some 3 units some 4 units etc, each unit is graded p=pass m=merit and d =distinction, so in the vlookup i changed each letter to a number so p =7 m=8 d=9 , which are divided into ½ or 1/3 or ¼ depending on number of units etc so whatever unit numbers they get say 1/2 P and ½ M etc for a 2 unit task will always add up to give a number that represents a grade mark that is looked up in the final part of my formula.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Lol i don’t know if that makes this any clearer.
Colin X