Spreadsheet (Excel) wizardry

[bernorange 8363]

I don't normally do anything too complicated with spreadsheets and I'm struggling to figure out how to write a formula to calculate what seems to be something rather simple.  Any help would be appreciated.

I've got 3 columns.  Column C has a list of numbers in ascending order (rows 1..n).  Column D has some numbers (also in ascending order, but that's not really important - rows 1..m where m<n).  Column E is where I want to put formulas in rows 1..m (to match with Column D).  The formula needs to calculate how many rows up (negative value) or down (positive value) in column C to find the corresponding value in column D.  For example:

Col C  --  Col D  --  Col E

2.2  --  12.1  --  3.87

3.7  --  15.6  --  4.14

6.9  --  15.6  --  3.14

9.0  --  15.6  --  2.14

12.6  --      --

15.0  --      --

18.9  --      --

[Jimbaround 975]

Do both columns C and D have the same data (just sorted differently), or do D and E have the same data?

What is currently in Column E?  You example doesn't seem to match your description.

[bernorange 8363]

Column C is a list of target values (percentages from 0 to 100).  Column D is a list of actual values.  Each row corresponds to a date (not shown, but explains why column C has many rows of data and column D has less).  Column E in my example is showing the value that should be calculated given the data in columns C and D.  I am trying to figure out how to write a formula for cells in column E that will calculate the values shown.

In other words, values in column E are the number of rows you would need to move/look in column C to find the corresponding value in column D.  In my example data, in row 2, the value in column D is 15.6.  From row 2, you would need to move/look 4.14 rows down to find 15.6 in column C.

[bschoolprof 6272]

Insert another column that tracks the row number ( 1 through n) before E. Call that new column E.  In the first row of new column F enter this formula; VLOOKUP(D1,$C$1:$E$n,3)-E1, fill in n, and copy down the rows.  You can then hide column E (the row tracker) if you want.  That should work I think.  

[Chris Dishman's Donut 192]

Couple of questions here:

• How do you move fractions of a row?  Those should be integral values - 4 rows down or 5 rows down, but not 4.xx
• Do the column D values match those in column C exactly?

If you're OK with being "close" as you look up values, and you agree that counting rows to two decimal places doesn't work, you can do this:

(I don't have a place to link from, so imagine that I've pasted a screen cap.)

Column E formula          =(VLOOKUP(D2,C:C,1))  This returns a % from column C that is less than your column D value.  Add "FALSE" to the end if values would match exactly between C and D.

Column F formula         =MID(CELL("address",INDEX(C:C,MATCH(E2,C:C,1))),4,2)   CELL will return the address of your VLOOKUP (i.e. $C$8).  MID(...,4,2) strips it down to only the row number

Column G formula        =row(E2) 

Column H formula        =F2-G2

 

[bernorange 8363]

@Chris Dishman's Donut - I need the fractions.  The value I'm calculating isn't specifically a "row" but how many "points" up or down on the scale represented by column C the value in column D lies.  The values in column D will very rarely match the values in column C exactly. 

Manually, I calculate the column E value like so (pseudo code):

fnColE( x) where x represents any row where col D has data

TargetRow = [ row where col C value > col D(x) ] -1

IF Col D(x) = Col C(x) THEN

   fn = 0

ELSE

   IF TargetRow = 0 THEN    'Col D value is less than first value in Col C

       fn = ( Col D(x) / Col C( 1)) - x

   ELSE

       fn =  TargetRow - x + (  (Col D(x) - Col C(TargetRow)) / (Col D(TargetRow+1) - Col C(TargetRow))

  END

END

Edited April 3, 2018 by bernorange

[bschoolprof 6272]

nevermind - I didn't fully understand your question.  My code is borked and too simplistic.  

[bernorange 8363]

@bschoolprof - No worries.  I appreciated the effort even if it wasn't exactly what I was looking for. 

[bernorange 8363]

FYI - @40acredropout sent me a PM with a link to a Googledrive spreadsheet with a potential solution for me.  Thanks to everyone who responded, but I think I can probably work out what I need from here.

Edited April 2, 2018 by bernorange

[Austinvines 318]

Timely thread! I have a workbook with multiple sheets for tracking results in my kids’ high school mountain bike racing league. There are separate sheets for each level (varsity, JV, soph, fresh) for both boys and girls. Each sheet has five races and includes name, school, and score. There is a bonus for racing in all five races and only your top four races count to determine the year’s champion per level.

I’ve got that figured out, but each race has podium for the top three schools and there will also be a champion school for the year. The school champion each race is based on the top four scores for high school but has to have at least one boy and one girl out of the four (b,b,b,g, g,g,g,b or two of each). I have no idea how to do this and what I am looking for help on. I’ll add a column for b or g to each sheet but have no idea if it’s even possible to program what I want to do over multiple sheets in the same workbook.

Any ideas? I am a total novice at xl but can copy and paste with the best of them. Thanks.

[mdmost 43050]

Would like to piggyback off this thread with my own Excel question. I have to keep track of test dates for a company's employees. On certain spreadsheets, the employer puts multiple years across different columns. It can verify from employee to employee meaning the column for each person will have a different year. I pull out the most recent test dates using =MAX(A1,C1,E1, etc.). Test scores also have to be pulled out and those are normally 90 or 100 and either are in the column before or after (B1, D1, F1, etc.). I can't use MAX for that since the most recent test might be 90. Is there a way to say if the MAX of one column is taken, take the column next to it with the score? These spreadsheets can have over 200 employees so pulling each number is a pain in the ass. 

[40acredropout 2902]

31 minutes ago, mdmost said:

Would like to piggyback off this thread with my own Excel question. I have to keep track of test dates for a company's employees. On certain spreadsheets, the employer puts multiple years across different columns. It can verify from employee to employee meaning the column for each person will have a different year. I pull out the most recent test dates using =MAX(A1,C1,E1, etc.). Test scores also have to be pulled out and those are normally 90 or 100 and either are in the column before or after (B1, D1, F1, etc.). I can't use MAX for that since the most recent test might be 90. Is there a way to say if the MAX of one column is taken, take the column next to it with the score? These spreadsheets can have over 200 employees so pulling each number is a pain in the ass. 

If I'm understanding you correctly, this should do.   What's in formula bar up top is the formula in highlighted cell (J2):

I2 (column labeled "Last Year") is calculating like you do-  max function.

You mentioned that sometimes the test score may be the column before.  If that is the case, change the "+1" at the very end of the formula to "-1" and you're good to go.

Edited April 3, 2018 by 40acredropout

[40acredropout 2902]

56 minutes ago, Austinvines said:

Timely thread! I have a workbook with multiple sheets for tracking results in my kids’ high school mountain bike racing league. There are separate sheets for each level (varsity, JV, soph, fresh) for both boys and girls. Each sheet has five races and includes name, school, and score. There is a bonus for racing in all five races and only your top four races count to determine the year’s champion per level.

I’ve got that figured out, but each race has podium for the top three schools and there will also be a champion school for the year. The school champion each race is based on the top four scores for high school but has to have at least one boy and one girl out of the four (b,b,b,g, g,g,g,b or two of each). I have no idea how to do this and what I am looking for help on. I’ll add a column for b or g to each sheet but have no idea if it’s even possible to program what I want to do over multiple sheets in the same workbook.

Any ideas? I am a total novice at xl but can copy and paste with the best of them. Thanks.

It can be done. 

For clarification-  in determining the champion school for a race, you are taking the four highest scores (subject to the bbbg/gggb constraint) from all eight events (V,JV,Soph,Fresh for boys and girls)?  It could be from any level-  ie, in theory if a school got first place in both boys and girls on the Freshman/JV level, they could win the event? How many kids are in each race/how many schools are there?  Or does that fluctuate.  

 

 

[Austinvines 318]

12 hours ago, 40acredropout said:

It can be done. 

For clarification-  in determining the champion school for a race, you are taking the four highest scores (subject to the bbbg/gggb constraint) from all eight events (V,JV,Soph,Fresh for boys and girls)?  It could be from any level-  ie, in theory if a school got first place in both boys and girls on the Freshman/JV level, they could win the event? How many kids are in each race/how many schools are there?  Or does that fluctuate.  

 

 

sent you a pm

You have it right. The school podium (1st, 2nd, 3rd) can be from any race, boys and girls, and from any levels. A first place in Varsity is worth 575 points and goes down from there, JV is out of 540 and soph/frosh are 500. The number of kids vary per race and i suppose there can be variability in schools that show up each race, but less so than the kids. Thanks for taking the time to look at this.