Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi
Please see the below table
name | id | number | level |
teh | 1 | 0 | A |
teh | 2 | 0 | A |
teh | 3 | 1 | A |
teh | 4 | 1 | A |
SO | 8 | 1 | A |
SO | 9 | 1 | B |
SO | 12 | 1 | C |
teh | 15 | 1 | B |
teh | 19 | 1 | B |
SO | 20 | 1 | C |
teh | 25 | 1 | B |
SO | 26 | 1 | C |
i want sum last 5 id numbers for each name -if sum>=5 show "ok" if not show "nok"
=if(RangeSum(FirstSortedValue(number,-id,1),FirstSortedValue(number,-id,2),FirstSortedValue(number,-id,3),FirstSortedValue(number,-id,4),FirstSortedValue(number,-id,5))>=5,'OK','NOK')
but i want to sum only the number with level as same as the last level
for example for "teh" the last record level (id=25) is "B" so this condition has to be check for last 5 id numbers with "B" level only
solution for "teh": (last record id=25 and level=b and number=1 so result = 1",n-1 record id=19 and level=b and number=1 so result=1
n-2 record id=15 and level=b and number=1 so result=1 ,n-3 record id=4 and level=A and number=1 so result=0
n-4 record id=3 and level=A and number=1 so result=0 final result =1+1+1+0+0=3 then its nok)
RESULT
NAME | RESULT |
teh | nok |
so | nok |
Output:
I value the insights and guidance you provide ,your answer is correct
do you know how to solve the question through expressions?
(my formula is so long and i think there is a better and short answer)