Hey,
I need help to achieve desired result out of the below shown data:
Requirement
1. Fill "Rolling Total" column : whenever value<threshold, Rolling total = [Value]+[Row+1:Value]+[Row+2:Value]+[Row+3:Value] (if threshold = 3) i.e addition till threshold value.
2. Also need to make sure it shouldn't add value from other "Name". meaning, no value from "Dog" to be added in "Cat".
3. In case there are no enough data to reach threshold limit, it should add all the next available values. For example. If my threshold = 4 but I have only 3 further value points, it should add all three next values.
I am using the Multi-Row formula with below code but it is adding values from "Dog"
If [Name] = [Row+1:Name] then
If [threshold] = 3 then
[Value]+[Row+1:Value]+[Row+2:Value]+[Row+3:Value]
else if [threshold] = 4 then
[Value]+[Row+1:Value]+[Row+2:Value]+[Row+3:Value] + [Row+4:Value]
else
0
endif
else 0
endif
I am aware that the start of the code is incorrect but I am not sure how do I change it to get desired result.
Solucionado! Ir para Solução.
The 'Group By' functionality in the Multi-Row tool should be able to help you. If you group by 'Name' it will only run the formula on the subset of records for 'Cat' and 'Dog' respectively. You should then be able to remove the [threshold] value from your conditional to make it simpler.
@SophiaF - This helped. Thank you so much!!
@Bolide - The macro looks impressive but the output is incorrect. I want "Rolling Total" to be updated only in the case Value<threshold. If Value>=threshold it should output zero. Can you update the macro or guide me where to apply this condition?? I really look forward to using this macro.
thanks!!
Hi @ayadav8, try this one
This is awsome.. Thanks!!!