Forum Discussion
Help with Excel formula
Hello I'm trying to insert a formula into a spreadsheet to show what % of events are 80% or higher of our recruitment target. We need it to: Search within a date range, Compare the value i...
HansVogelaar I've added an example table into the original post.
Thanks for your help.
With your sample data starting in A1:
=SUM((A2:A27>=F1)*(A2:A27<=F2)*(C2:C27>=80%*B2:B27))/SUM((A2:A27>=F1)*(A2:A27<=F2))
Format the cell with this formula as a percentage.
Thanks again for your help. What would be in cells F1 and F2 that you refer to? Also is it possible to link the entire A and B column as opposed to a specific cell range?
F1 is the start date and F2 the end date - I should have mentioned that. I used those because you mentioned a date range...
Thanks again HansVogelaar and SergeiBaklan - I've managed a workaround.
To combine in one formula
=LET( months, UNIQUE(EOMONTH(+Source[Date], 0)), VSTACK( {"Months","Result"}, HSTACK( months, SCAN(0, months, LAMBDA(_,v, SUM( (EOMONTH(+Source[Date], 0)=v)* ( Source[Shifts recruited]>=0.8*Source[2024 Target] ) )) ) ) ))With PivotTable if you use data model we may add the measure
Result:=SUMX(Source, 1*(Source[Shifts recruited] >= 0.8*Source[2024 Target] ) )
