Seamus2626

asked on

Hi,

Attached is a file where in N8 I should be returning Yes, but am getting No

the idea is the formula flags Yes if Abc has a value of 1 or greater in 2 of the last three months

So in my attached example, Abc has breached in Oct and Sep so should be returning a Yes

EE.xlsx

Attached is a file where in N8 I should be returning Yes, but am getting No

the idea is the formula flags Yes if Abc has a value of 1 or greater in 2 of the last three months

So in my attached example, Abc has breached in Oct and Sep so should be returning a Yes

EE.xlsx

Last Comment

OFFSET is doing what you told it to do (COUNT counts all numbers including 0). Why are you using OFFSET at all instead of simply using the last three columns?

ASKER

As its a pivot table, next month will be in the next column, im dropping that formula in by VB - So my vb finds Grand Total in the pivot, offsets and finds the right cell to drop the above formula into

So I can use sum as the columns will be different

So I can use sum as the columns will be different

I too am curious why you do just add the last three months of values and test. However, maybe this example is oversimplified for the purposes of presenting here. If you do wish to continue using this, you only need to change the anchor points of your test ranges (In N7):

**=IF(C7="","",IF(SUM(OFFSET**(C7:L7,0,COUNT(C7:L7)-3,1,3))>=2=TRUE,"Yes","No"))

This, however, is still incorrect. Your test is simply testing the sum of the last three values to see if it's greater than or equal to 2. What if you had two months of zero values and one month of 2 or more? You'd see a "Yes" result, which is not what your test looking for.

Instead, I recommend this function instead (again, in N7):

**=IF(C7="","",IF(SUM((J7=0)**+(K7=0)+(L7=0))>1,"No","Yes"))

This not only tests for the last three occurrences, but also whether two or more are non-zero.

Regards,

-Glenn

This, however, is still incorrect. Your test is simply testing the sum of the last three values to see if it's greater than or equal to 2. What if you had two months of zero values and one month of 2 or more? You'd see a "Yes" result, which is not what your test looking for.

Instead, I recommend this function instead (again, in N7):

This not only tests for the last three occurrences, but also whether two or more are non-zero.

Regards,

-Glenn

Okay, just read your reply about this being applied to an expanding PivotTable. Revise my suggested formula to this (in cell N7, copied down):

**=IF(C7="","",IF(SUM((OFFSE**T(O7,0,-4)=0)+(OFFSET(O7,0,-3)=0)+(OFFSET(O7,0,-2)=0))>1,"No","Yes"))

The logic is the same as my last example, except now, it specifically offset the three columns to the left of the formula, skipping the grand total column.

Regards,

-Glenn

The logic is the same as my last example, except now, it specifically offset the three columns to the left of the formula, skipping the grand total column.

Regards,

-Glenn

ASKER

Hi Glenn,

When I put the suggested formula in, it is returning "Yes" values where it should be No.

Is it because the formula is counting the Grand Total?

Ive attached the screenshot

Thanks

EE.docx

When I put the suggested formula in, it is returning "Yes" values where it should be No.

Is it because the formula is counting the Grand Total?

Ive attached the screenshot

Thanks

Are the dates true dates or just text representation of dates? If just text can they be converted to dates?

With TRUE dates, you could use the SUMIF formula to calculate the total for the last three months by comparing the column header row to a current date cell.

Also with the formula being populated by VB, if you use R1C1 notation in the formula rather than A1, it will adjust the column references appropriately dependent on the column it gets populated into.

Alternatively, if you are only interested in the last three months, you could filter the pivot so that it only shows the last three months, there would only then be the same three columns each time. Not sure how you apply the filter within the VB script.

Thanks

Rob H

With TRUE dates, you could use the SUMIF formula to calculate the total for the last three months by comparing the column header row to a current date cell.

Also with the formula being populated by VB, if you use R1C1 notation in the formula rather than A1, it will adjust the column references appropriately dependent on the column it gets populated into.

Alternatively, if you are only interested in the last three months, you could filter the pivot so that it only shows the last three months, there would only then be the same three columns each time. Not sure how you apply the filter within the VB script.

Thanks

Rob H

ASKER

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

ASKER

Thanks!

Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K

Questions

--

Followers

--

Top Experts

Get a personalized solution from industry experts

TRUSTED BY