I have attached a snippet of my data and I need to create a series of calculations based on fields that come before it. Sheet1 contains what I was given and Sheet2 is my desired result with the formulas.
I essentially need to calculate values for [Cum PD - 13] and onwards.
How do I create formulas like the ones in Sheet2 without having to enter it manually? The [df#] columns could go up to [df300]. In the first row it only goes up to [df24] which is why the calculation only goes up to [CumPD - 24].
The formula is: [CumPD - X] = [CumPD - X-1] / [dfX], so for [CumPD - 13] it would be [CumPD - 12] / [df13] and for field [CumPD - 14] it would be [CumPD - 13] / [df14] and so on.
Thanks in advance!
Solved! Go to Solution.
Here is a very rough way of doing it, seems to work though so you could adapt for your needs!
Transpose the data vertically so you can use the multi-row formula, then cross tab it back to the original layout.
User | Count |
---|---|
52 | |
27 | |
25 | |
24 | |
21 |