Lookup & average

[Ann Scharpf]

I can see that there are a lot of items on this BB related
to lookup functions. I've been reading them and still
can't figure out how to set this up.

I have a named range (Billing Start Date) on Sheet 2.
On sheet 1, I have a 25 column table: top row is billing
start dates, row six is the total amount billed for that
period. (Cell address for range is A35:Y41.)

I am trying to do the following:

1. Identify the Total Billing Amount whose date matches
the Billing Start Date from sheet 2.
2. Average the current and previous 2 Total Billing Amount
values.

I think I need to do an offset function to grab the 2
adjacent cells for the averaging. But I am having trouble
getting Excel to identify the CELL ADDRESS of the current
Total Billing Amount. I have gotten the HLOOKUP to give
me the value of the current bill but that's as far as my
understanding has taken me -- and it doesn't really help
because I still need the cell address.

I'd appreciate any help you can give me. Thanks.

Ann Scharpf

 

[Ken Wright]

For starters the name as you have listed it is not valid, so I assume you mean something like
Billing_Start_Date

Try the following formula:-

=IF(MATCH(Billing_Start_Date,Sheet1!A35:Y35,0)=1,AVERAGE(OFFSET(Sheet1!$A$35,6,MATCH(Billing_Start
_Date,Sheet1!A35:Y35,0)-1,,-1)),IF(MATCH(Billing_Start_Date,Sheet1!A35:Y35,0)=2,AVERAGE(OFFSET(She
et1!$A$35,6,MATCH(Billing_Start_Date,Sheet1!A35:Y35,0)-1,,-2)),AVERAGE(OFFSET(Sheet1!$A$35,6,MATCH
(Billing_Start_Date,Sheet1!A35:Y35,0)-1,,-3))))

If you had had two blank columns to the left of your table, then the following would have been
sufficient, but as it is it will error oout because the offset tries to go back past Col A if the
matched date on sheet 1 is in Col A or B.

=AVERAGE(OFFSET(Sheet1!$A$35,6,MATCH(Billing_Start_Date,Sheet1!A35:Y35,0)-1,,-3))

 

[Ann Scharpf]

Yes, you are right, I didn't type the actual range name
(Curr_PP_Start_Date) because I was being a lazy typist &
just trying to get the point across.

I am building this formula in Sheet1, so I don't need to
have all the "Sheet1!" parts of this formula, do I?

Ann

-----Original Message-----
For starters the name as you have listed it is not valid,

so I assume you mean something like

Billing_Start_Date

Try the following formula:-

=IF(MATCH(Billing_Start_Date,Sheet1!A35:Y35,0)=1,AVERAGE (OFFSET(Sheet1!$A$35,6,MATCH(Billing_Start
(Billing_Start_Date,Sheet1!A35:Y35,0)=2,AVERAGE(OFFSET(She
1,,-2)),AVERAGE(OFFSET(Sheet1!$A$35,6,MATCH
(Billing_Start_Date,Sheet1!A35:Y35,0)-1,,-3))))

If you had had two blank columns to the left of your

table, then the following would have been

sufficient, but as it is it will error oout because the

offset tries to go back past Col A if the

 

[Ann Scharpf]

IT WORKED! I'm sure you're not at all surprised but I am
because I have no earthly idea exactly what that formula
SAYS.

Now I have to study it for a while to build an
understanding of why it worked.

Thanks for taking the time to help me.

Ann Scharpf

 

[Ken Wright]

You had originally asked for the formula to work from a different tab, so I put a sheet name in
there so you could see the format it would need to be in. If the sheet name had any spaces you
would need to put in single quotes around the sheet name, but it would probably be easier to take
the space out of the actual sheet name on the tab, put in the formula into the sheet and then put
back the space in the actual sheet name on the tab. The formula will self adjust and save you
having to do it.

 

[Ken Wright]

I'll explain the shorter one first because the other is simply the same thing three times with a
couple of small tweaks to change the width of the range it looks at depending on what column the
range starts in.

=AVERAGE(OFFSET(Sheet1!$A$35,6,MATCH(Billing_Start_Date,Sheet1!A35:Y35,0)-1,,-3))

The first thing to look at is the innermost formula:-

MATCH(Billing_Start_Date,Sheet1!A35:Y35,0)-1

This takes the value in your range for the start date and then matches it to one of the values in
the range A35:Y35. If the date matches the 3rd value then it will return a 3, if it matches the
5th value it will return a 5. the -1 at the end takes 1 off whatever value the MATCH returns, so
that if you had for example 5 values you were matching against, the possible values that would get
returned would potentially be 0,1,2,3,4 (You'll see the reason for taking the 1 away in a minute)

So, you now have one of 5 values that will be passed to the OFFSET function as an argument. The
OFFSET function starts at a predetermined cell, in this case the very first value in the list of
dates on sheet 1, and then offsets from that however many rows and columns you tell it to. the
OFFSET function takes the following format:-

=OFFSET(reference,rows,cols,[height],[width]) with height and width being optional arguments.

=OFFSET(A1,6,1,,3) will look at the range that is 6 rows down from A1, 1 column over (ie B), and
3 cells wide from and including that cell. By making the 3 a negative 3, it will look at the
range that is 3 cells wide back from and including that cell (Hence the problem if the cell it
starts working back from is in Col A). the reason for the -1 on the MATCH function should now be
appearent, as if you start at A1 and you match that cell and then want the data that is 6 rows
down, you do not want to offset any columns at all, so where MATCH would potentially return 1 if
it matched the first value, if you passed 1 to the offset function you would actually get the data
in the next column instead of the one you wanted. I could also have simply left the MATCH
function as it was and put a -1 in the OFFSET(,,cols,,) argument which would have had the same
effect.

By using the IF function, I have simply said that if the MATCH formula returns a 1 then I am
obviously in Col A, so change the width argument to -1 (I could have omitted it, but it is easier
to see this way), or if it is a 2 then I am in Col B, so only look at a width of 2, otherwise give
me a width of 3.

The AVERAGE bit should be obvious.

Hopefully that is not too confusing.