Solved

Offset not working

Posted on 2014-12-18
9
43 Views
Last Modified: 2014-12-19
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
0
Comment
Question by:Seamus2626
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40507376
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?
0
 

Author Comment

by:Seamus2626
ID: 40507422
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
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40507436
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
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40507450
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((OFFSET(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
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Seamus2626
ID: 40508790
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
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40508810
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
0
 

Author Comment

by:Seamus2626
ID: 40508846
Hi Rob,

Ive posted my ss

Many thanks
EE.xlsx
0
 
LVL 32

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 40508899
Similar to what you uploaded previously, but still doesn't answer the question as this is not a pivot table; it may be a copy and paste of a pivot.

Dates in source data - are they true dates? Looking at the result I would say not, how is the source data formatted so we can change to true dates?

With that said, I have worked a solution for you. See attached.

In your original row 12 gives a Yes eventhough it is only one occurence in the last 3 months. Is that correct?

In column P, I have put the R1C1 equivalent. When pasted into a cell this will always use a range starting at column C and ending in column prior to formula, ie the Grand Total column but as there is no date in the Grand Total column it will ignore it.

In row 3 I have converted the text dates to proper dates assuming 1st of month.

Thanks
Rob H
EE-1-amended.xlsx
0
 

Author Closing Comment

by:Seamus2626
ID: 40509343
Thanks!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now