Excel Formula Help

>>8439053
>using excel

Consider purchasing some tweed and getting knot tying lessons

>>8439065
Cheers - probably not much help with the formula though

>>8439053
Try this formula
=IF(B3=1,IF(C3="A",20,IF(C3="B",18,IF(C3="C",16))),IF(B3=2,IF(C3="A",14,IF(C3="B",12,IF(C3="C",10))),IF(B3=3,IF(C3="A",8,IF(C3="B",6,IF(C3="C",4))))))

>>8439334
Or you could be really fancy and do this
=INDEX($I$3:$I$11,MATCH(B3&C3,$G$3:$G$11&$H$3:$H$11,0))
Be sure to press ctrl+shift+enter after pasting it in to designate it an array formula.

>>8439065
what do you use?

>>8439053
>Excel

>>>/g/tfo

Use VBasic macros, or whatever they're called now.

Maybe you just can't nest it that deep

% run in R where the scores are in a tab-delimited text file with the column headers:
% "target","arrow","zone"
% do not make a score or total column, we'll make that here
% for the "zone" column, code "-" as "-"
% for the "arrow" column, code "-" as 0
options(stringsAsFactors=FALSE)

% set up the key
key <- data.frame(arrow=c(1,1,1,2,2,2,3,3,3,0),zone=c(rep(c("a","b","c"),3),"-"),score=c(20,18,16,14,12,10,8,6,4,0))

% import the scores and add the new columns, initializing all values to zero
scores <- read.delim("scoreTable.txt")
scores$score <- 0
scores$total <- 0

% iterate across the rows
for (eachRow in dim(scores)[1]) {
% set up a boolean to subset the key data frame
keySubset <- (key$arrow == scores$arrow[eachRow]) & (key$zone %in% scores$zone[eachRow])

% get the appropriate current score
currentScore <- key$score[keySubset,]

% add it to the output table
scores$score <- currentScore
if (! eachRow == 1) {
scores$total[eachRow] <- scores$total[eachRow-1] + currentScore
} else {
scores$total[eachRow] <- currentScore
}
}

% write the output table
write.table(scores,"scores.txt",quote=FALSE,row.names=FALSE)

>>8439437
This is awesome but I have no idea what you did there

>>8439450
it's a short R script to read in a file containing the scores, perform the necessary summations, and output a new file

I haven't read the thread so I'm not sure if you have your answer yet, but it seems like you could do this in excel if you change a few variables around. Firstly, replace every A with a 6, every B with a 4 and every C with a 2. Then, replace your arrow numbers like so: 3=>2, 2=>8, 1=>14. Then your score is just the addition of the two rows.

>>8439334
Brilliant! Worked a charm!

Thank you

>>8439053
Why did you separate arrow and zone into separate cells? It seems to me to be simpler to create a single cell and use a vlookup. Lookup Values "1A,1B, 1C, 2A, 2B, 2C, 3A, 3B, 3C".

You can insert a column between H and I with the following formula: =Gn&Hn where is the row number. Then insert between C and D: = Bn&Dn.

Then the formula in D (now column E) = vlookup (Dn, I$3$:J$11$,2)

>>8439661
Using index and match to do lookups is better because the range doesn't have to be in alphabetical/numeric order. Plus you don't need to combine arrow and zone into one. Check out >>8439371 it does the same thing as vlookup, just uses index and match.

OP here - all sorted - thank you for your help