replace score needed

[score]

I have 4 score boards, white. blue, yellow, green, on the score board white i have heat totals points in cell E7 which is the sum of the two highest average points in the range of cells H10:H19 (H10,H11) and in cel H7 win by 2.50 over blue who is in second place with heat total points of 15 on cell M7, which is the sum of the two highest average points in the range of cells P10:P19 (P10,P18), then on P7 i need to return what blue needs to be on 1st place, on the formula i have return 2.51 (which is not rihgt) what i need to return in cell P7 is 9.51, need to replace the lowest of the two highest average points for 9.51 which is what blue needs to get to 1st place, on score board yellow i have heat total points of 14, then on H23 should return 9.61 and not 3.61 as it is now, on score board green need to return 10.51 on cell P23, hope i explained right and make sense, many thanks in advance for any help on this formula. I have attached a file for better reference.

[FDibbins]

actually that was quite a mouthful, but i will try and make sense out of it and see if i can help you

[FDibbins]

oooo......k. you have circular references up the ying yang on sheet1!!

F47:F56 and J47:J56 - in each of those cells, the max() function references the cell that it is in,
for instance in F47, you have =MAX(W11:$W$20,F47)
in AA5, you have =COUNTIF(Z1:Z3,AA5)
in B1 you have =IF(A1="","",IF(B1="",NOW(),B1))

I would suggest you fix these 1st, before trying anything else - circular references can cause all sorts of wierd answrs to pop up where you least expect them to.

1 quick way to find these circ references is to look at the bottome left of the screen, next to "ready" you will see "Circular Reference AA28" - or whatever the cell ref is. go to that cell and fix the circ ref there, then move to the next 1, until you have foxed them all

let me know when you have sorted them out, and we can see what you need (fixing them may even solve your problem?)

[score]

Hi FDibbins, sorry but i have attached the wrong workbook file, find here the right one

[FDibbins]

ok your explaination above is a bit unclear to me. can you walk me step-by-step through what you are doing, what you have, and whats not working for you?

[score]

Hi FDibbins, i need a formula to return the value of by how many points is ahead to who is winning between all four colors (white, blue, yellow, green) (win by) and to return how many points needs to be on first, to who is in 2nd, 3rd, and 4th place, the formula is in H7,P7,H23,P23, what i have is that the formula make reference to col E7,M7,E23,M23 to return the value of winning by and that part is ok, but to return the value that needs to be in first place needs to make reference to the range of cells H10:H19, P10:P19, H26:H35, P26:P35, on the workbook you can see that white have a total heat points of 17.50 col E7 and blue have heat total points of 15 col M7 and the formula in col P7 return 2.51 but in reality needs to return 9.51 because the value of 15 on col M7 is the sum of the two highest values on the range of cells P10:P19, in this case P10 with average points of 8 and P18 with an average points of 7, the formula on P7 need to return 9.51 because is the score that adds up to the second highest to sum 17.51 to be on 1st place over white col E7.
In resume, the formula needs to make reference to col E7,M7,E23,M23 to return the value of winning by and make reference to the range of cells H10:H19, P10:P19, H26:H35, P26:P35 to return the value that needs to be in 1st, hope this clear things up

[score]

Does anybody out there know the solution for this value returns?

[score]

Does anybody out there know the solution for this value returns?

FDibbins, Thanks for taking of your time to try and help me with this formula, i still don't know if is possible to make a formula to get the results i need, thank you very much for your good intentions.

[score]

Please! Is there is any body out there who could may help me with this firmula i looking for?

[vlady]

if you add second largest to 2.51 that's 9.51 is that what you want???

in P7 include this to the end of the formula +LARGE(P$10:P$19,2)

[score]

Vlady this give me the right value return and i really appreciate your time to help me with this, if you look to the workbook, what i looking for is that every time the average points change the situation return the value of for how much is winning, looking at the score board white col H7, is winning by 2.50 over blue col P7, i added what you said and give me exactly what it needs to be in first, the problem is when the situation changes betwen the competitors i need the formulas to change to who is winning by x amount of points to who is second place, how much need to be in first and to who is in 3rth and 4th how much need to be in second because the first two position advance to the next round, hope this make sense to you, but if you look at the workbook will be easier to understand, thanks in advance for any further help

[vlady]

Sorry for the delay,
3rd and 4th would then look to the 2nd place for the difference is that it?
can you look at the attached maybe the if statements would work the way trough the goal...


Score Board 4 Final-1 (version 1) (2).xlsx

[score]

vlady, that it, Many many thanks for taking of your time and solved this for me, really appreciated, cheers!!

[vlady]

Your welcome thank you too. Nice logic made me twist.

[score]

Hi vlady, i been checking if everything is working good, but when i changed the situation of the competitors and white got on 4th place with 17.50, the return on col H7 should be 8.01 to get to 2nd place and not 7.91 as showing here, if any of the 4 competitors get 3rd or 4th needed to return what need to be in 2nd and if any of the competitor is on 2nd place need to return what need to be in 1st, please check attached workbook for a better reference, white is in 4th position and i need it to return 8.01 and not 7.91 as showing here, hope this make sense and be able to fix this, sorry for the inconvenience

[score]

Forgot to include the attachment, here it is

[vlady]

Ok.. 3 cells been updated for the formula (colored yellow) can you check it now. thanks.

updated Score Board 4 Final-1.xlsx

[score]

Hi vlady, really appreciated your help and the time you took for this, thanks to this update i figure it out, thanks again.