Formula to calculate report requirements based on activity since last report filed.

[Eurus53]

Hello --

First off, shout out to the forum here. I came here with a complicated question almost a year ago and got a great answer. Since then, I've really increased my excel skills a ton, but after days of trying to crack this problem, I'm reaching out for help.

###

Attached is an example of what I am working on.

Each row is a different entity and in each of the subsequent 6 columns you see how much financial activity that entity engaged in during the months listed in the header.

In the next 6 columns is where I am trying to apply this formula, which I imagine will wind up looking something like =IF( >200, "YES","NO") but more complicated obviously.

Here is the rule:

If the entity incurred more than an aggregate of $200 of activity since the last time they were required to file a report, then they have to file a report for the month where the aggregate of $200 was surpassed.

So for example, if I have spent $500 in December, I would be required to report that immediately. But if I spent only $100 in January, I wouldn't have to report that until I spent an additional $100 in February or March. Make sense?

I used the first 4 columns to provide an example so that someone might be able to help me.

Another way of describing it might be: I need a way to aggregate only amounts under $200 that have been incurred since the last time an aggregate of $200 was reached.

Best,

-CE

[JohnTopley]

This

=IF(B2>200,"Yes","No")

copy across and down

Or

=IF(INDEX($B2:$G2,,MATCH(I$1,$B$1:$G$1,0))>200,"Yes","No")

Copy across and down


Ensure all heading are consistent: I prefer to use dates e.g. 01/01/2018 (Jan 1) and format as "mmm"

[Eurus53]

Hey John --

Thanks for the reply!

So yes, =IF(B2>200,"YES","NO")is the correct formula to be applied to the entire H column, so that is squared away. Good.

But the remainder are a bit more complicated given the rule. Your formula =IF(INDEX($B2:$G2,,MATCH(I$1,$B$1:$G$1,0))>200,"Yes","No")looks to me like it might accomplish the rule via formula, but when I put it in I-2, I get the #N/A error. I tried tinkering with it to get it to work to no avail, any tips for me to get it to work in cell I-2 of the example file?

[JohnTopley]

Ensure all heading are consistent: I prefer to use dates e.g. 01/01/2018 (Jan 1) and format as "mmm"

i.e Dec and Dec not December and Dec

[Eurus53]

Gotcha. Thanks!

I did what you said with formatting and applied the formula, but it only returns the value "TRUE" for every cell. (See attached)

Can you send me the sheet that you were able to get to work so I can see what I'm doing wrong?

[JohnTopley]

In H2

=IF(INDEX($B2:$G2,,MATCH(H$1,$B$1:$G$1,0))>200,"Yes","No")

Copy across and down

I don't know you managed it, but you had two formula in one cell as below:

=IF(INDEX($B2:$G2,,MATCH(H$1,$B$1:$G$1,0))>200,"Yes","No")=IF(INDEX($B2:$G2,,MATCH(H$1,$B$1:$G$1,0))>200,"Yes","No")

so this will return TRUE as they are indentical

[Eurus53]

Thanks again John, I appreciate your patience.

So I did apply it as you said.

It's giving me "TRUE" and "FALSE" readings instead of "YES" and "NO", which is odd to me looking at the construction of the formula. Also, its not doing what the formula needs to.

Did you see the first 4 rows that I gave in the first example? Did the formula return the same answers that I put down for you? Unfortunately they didn't for me.

[JohnTopley]

See attached.

[Eurus53]

Thanks! I'm not sure how i screwed up on the earlier version.

Unfortunately though, this doesn't quite accomplish the rule: "If the entity incurred more than an aggregate of $200 of activity since the last time they were required to file a report, then they have to file a report for the month where the aggregate of $200 was surpassed. "

See for example Row 18 and "Group 17". Because the $200 threshold was passed in January (since the filing of the last report), a report was due for that month, so I18 should read "Yes".

[JohnTopley]

For (as an example) row 18

in H19

=IF(SUM($B18:B18)>200,"Yes","No")

will give results of "No", "Yes" ......."Yes"

is this correct i.e once passed the 200 limit, it is always yes?

Or having filed in January, it is not filed again until the next 200 threshold is reached?

[Eurus53]

John --

Here I uploaded what I think we should work off of, because the formula you came up with (=IF(B2>200,"Yes","No")) is already correct and will always be correct for column H.

Once something is filed in January, nothing would have to be filed again until the aggregate total in subsequent months reached $200. Does that make sense? I understand this is confusing and I appreciate your working with me. This is a challenging formula to come up with, even though its fairly easy to do manually. If I didn't have to do it over millions of rows of data it wouldn't be an issue.

[bebo021999]

If I interpreted correctly, If Dec is "Yes", Jan is "Yes", Feb is "No", then March will take into account the last "Yes" in Jan, sum values from Feb to March, the compare with 200?

Try in H2:

Please Login or Register  to view this content.

In I2:

Please Login or Register  to view this content.

Drag I2 accross
Drag all down.

[Eurus53]

If I interpreted correctly, If Dec is "Yes", Jan is "Yes", Feb is "No", then March will take into account the last "Yes" in Jan, sum values from Feb to March, the compare with 200?

Try in H2:

Please Login or Register  to view this content.

In I2:

Please Login or Register  to view this content.

Drag I2 accross
Drag all down.

Bebo --

Your brain functions on a much higher level than I could ever hope to. This is perfect.

Thank you for your help too John!

CE

[JohnTopley]

All credit to Bebo: perhaps you can "Add Reputation" for him.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.