create custom formula

Question

I would like to create this custom formula to calculate a points awarding system that automatically calculates. P and A are the variables in the formula.

Is this possible?

P = event finishing position
A = event attendance
Φ = fleet factor (B Fleet - 0.33; C Fleet - 0.15)

pevenden · Answer

djclements That looks great!I will play with a bit (I've created a new 'test' sheet so I don't mess up your formulas... )and I think it will do the job perfectly...thanks everyone for your help in sorting this out...

hansvogelaar · Answer

pevenden&nbsp;
What is f(x)? There is no x in the expression on the right.
And why do you sum from x = 1 to 5?

pevenden · Answer

HansVogelaar&nbsp;Yeah, I was not much at math... so alot of the characters don't mean much to me... however...The formula creates a point system for overall points for a sailboat racing season. The points awarded are dependent on the number of entries in the race, and the position of each boat in that race.&nbsp;there is a graph that has the detail but I am trying to put the formula into excel for easier calculation...&nbsp; if it works that is....&nbsp;&nbsp;does that help?&nbsp;

peiyezhu · Answer

. P and A are the variables ?or constant?I guess need some specific variables and expected result to understand this formula.

djclements · Answer

pevenden&nbsp;If you have Excel for MS365, you can achieve the same results as the graph you've shared with the following formula:&nbsp;=LET(n, B1, k, B2,
φ, IF(ISBLANK(k), 1, k),
MAKEARRAY(n, n, LAMBDA(p,a,
   IF(p&gt;a, "", IFERROR((p*(1-((50*a+4800)/98))+((a*(50*a+4800))/98)-1)/(a-1), 25)*φ))))&nbsp;...where the attendance (A) is in cell B1 and the fleet factor (φ) is in cell B2. The results would look something like this:&nbsp;MAKEARRAY Chart with Conditional Formatting&nbsp;Conditional Formatting was applied to range C5:V24 to shade the empty string results blue using the following formula:&nbsp;=AND(NOT(ISBLANK(C5)), C5="")&nbsp;You could also create a custom LAMBDA function in Name Manager to award points to each team based on their finishing position for each race. For example, you could create a function called XPOINTS using the following formula:&nbsp;=LAMBDA(a,p,[φ],
   LET(
      k, IF(ISBLANK(φ), 1, φ),
      IFERROR((p*(1-((50*a+4800)/98))+((a*(50*a+4800))/98)-1)/(a-1), 25)*k
   )
)&nbsp;...then use it as follows:&nbsp;Custom LAMBDA Function&nbsp;The formula used in cell D4 in the screenshot above is:&nbsp;=XPOINTS(COUNTIF($A$4:$A$20, A4), B4, $B$1)&nbsp;...where COUNTIF is used to calculate the total attendance (A) for each Race No.&nbsp;As for the Season Standings section shown above, the following formula was used in cell F4:&nbsp;=LET(
u, UNIQUE(C4:C20),
a, SORT(HSTACK(u, SUMIFS(D4:D20, C4:C20, u)), 2, -1),
HSTACK(SEQUENCE(ROWS(u)), a))&nbsp;I've also attached the workbook for your convenience. Cheers!

Forum Discussion

create custom formula

Share

Resources