I need to look up a formula to calculate the value in the "Score" column. There are different formulas based on the skiers division. Essentially, if a skiers division is M1 then formula A would be used. If a skiers division is W4 then formula B would be used.
Thank you for your help.
Your spreadsheet does not contain any information of which formula you want to use for each division.
Basically what you need to to is:
F3 --> =IF(B3="M1", FORMULA-M1, IF(B3="W4", FORMULA-W4, ""))
Is what you're looking for?
I hope it help.
...If my answer helped you, Please, click on. * Add Reputation (at left)
Best regards.
Marc?lio Lob?o
This should give you an idea of how to proceed. This formula with the formulas for M1 and W2 added would be entered in F3 and filled down.
=IF(B3="M1",FORMULA FOR M1,IF(B3="W4",FORMULA FOR W4,"")
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
My apologies. I didn't think the formulas mattered and I don't think a nested IF statement would work as there are potentially 28 unique divisions that the competitors could be in.
Is there a way to have a separate sheet with all 28 formulas listed and based on the skiers division that formula is "selected" and their score is calculated using the values in the "Speed", "Buoys" and "Linelength" cells?
mshaw200, Good afternoon.
We gave you this suggestion based on your example of two(2) possible divisions.
As you have twenty-eight(28) divisions, possibly the solution should be another.
To facilitate our understanding of your real need, please show us the formula for the M1 and W4 divisions that are in your example.
This will facilitate the development of an effective solution to your spreadsheet work.
I have added the formulas. Neither of these division formulas incorporate the "Speed" however other divisions formulas will.
I just need to figure out how to "activate" or select a formula based on a skiers division.
Create a chart for the Linelength with the values for M1, W4 etc across the top. Not knowing what Linelength is I started at 1 and went to 40 down column M and added values for M1 and W4 to the chart. So, the chart is M3:O81 for this example.
The score would then be calculated as follows which looks up the value in column E and adds the appropriate value do the value in column D for the Division:
Formula:
Please Login or Register to view this content.
mshaw200, Good morning.
As you said it has 28 divisions, so you can use the efficient method of newdoverman in an indexed way.
Try to use:
I hope it helps.
Please Login or Register to view this content.
I didn't pick up the 28 divisions....good spot. The VLOOKUP range must also be increased using that method.
Here is a method using INDEX and MATCH. Fill in the chart with the Divisions and their appropriate values for the Linelengths and use this formula to extract the correct data.
Formula:Please Login or Register to view this content.
Here is the VLOOKUP method expanded to accommodate a complete chart of 28 Divisions.
Formula:Please Login or Register to view this content.
Both of these will check to see that a Division has been assigned when a competitor has been added to the list. If a Division is not assigned, no calculation will take place. You don't have to keep changing the chart for the number of divisions as it is predefined in dimension and the Divisions can be entered in any order wished as long as the correct values are entered under each Division.
I decided to add some additional information to the spreadsheet. There are 5 formulas used between the 28 division.
I have set up 1 competitor for each of the formulas.
With this information. Does it change your recommendation of how to handle my scenario?
For example I would like the spreadsheet to know that if I were to change Skier "Kristen Baldwin" from G2 to W4 that it would automatically know that Formula 2 should be used instead of Formula 3.
Thanks
Having examined your 5 formulae, I can't figure out why you have 2 sections to each formula. There just doesn't seem to be any logic to this. You say that you have 28 divisions but in reality they only make use of 5 formulae.
The division determines the formula and yes there are only 5 different formulas.
What do you mean "2 sections to each formula"?
In some events there are situations where someone in M1 (using formula 1) will be competing against someone in W3 (using formula 3). Their "score" is determined by their specific formula.
Having the ability to "activate" or use a specific formula based on a skiers Division become important in a bracketed format where there is no way to predict who will advance and thus which formula will be needed.
I have come up with something that appears to work with your example. It looks up the Division, finds the formula for that Division and applies it to the data in the row.
I have assigned a name to each of the formulae, changed the value for each formula from F1, F2 etc to simply 1, 2 etc. Once doing that, I used VLOOKUP of the Division to determine the formula to use. This was then used in the function CHOOSE to find and apply the formula.
Formula:Please Login or Register to view this content.
To answer your previous question about changing divisions. Yes, if you change the division, the appropriate formula will be applied to the new division.
Last edited by newdoverman; 09-13-2015 at 03:41 PM.
If you look in the Name Manager, you will see Formula1, Formula2, Formula3, Formula4, Formula5. Each of these names have your formula as if they were in row 3. This allows each formula to be assigned to any row and the relative references will be updated so that the formula works no matter what row it is in.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks