Jump to content

Spreadsheet (Excel) wizardry


Recommended Posts

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  --      --

magnets_c.jpg

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.  

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

@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 by bernorange
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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):

Nk4NQCo.png

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 by 40acredropout
Link to comment
Share on other sites

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.  

 

 

  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.



×
×
  • Create New...