Excel 2010: Looking for a Function

[ladyck3]

Hi Gurus...

I have a question on a function, if there is one for what I am attempting to do and if so what would that be.

I have a "scorecard" that needs to be filled out with the scoring at the top of the page as follows:

[tt]AHT
10 7.00
9 7.50
8 8.00
7 8.50 8.50 ACTUAL AHT entered manually
6 9.00
5 9.50
4 10.00
3 10.50
2 11.00
1 11.50
0 >11.50

SCORE Manually Entered here

The first column is the score
The second column is the AHT Range
The third column is the actual AHT...
so 8.50 has a score of 7 both of which are manually entered
[/tt]

Then below this space is a month by month score of each.
What I have done for the past 8 yrs is drudginly scrolled up and down re-entering the data in the month over month horizontal table. I want to set this up so that when I enter the data at the top it is replicated at the bottom. I can then just copy/paste special the values so they don't change the next month with the new data is entered.

I have the SCORE number replicated with an =$E$16 formula but my problem is the 3rd column, as this will vary every month....
How do I capture in the bottom seciton, whatever entry is placed in that column? Basically there will be one entry but can be in any of 10 rows.... what function do I use to grab that one entry?

I have an image but no place to post the file to link via URL... sorry

Thanks in advance....

ladyck3
aka: Laurie

 

[SkipVought]

Laurie,

What is the Score if the Actual is 6.9 or is 7 the MINIMUM value?

Generally you could use the MATCH() & INDEX() functions to return a score to any AHT. Is that the objective?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

The Score cannot be 6.9, as this sheet (I hate it) will take a value within the limits.

If a score AHT is 9:03 the score would drop to 5 rather than 6, as it did not meet the 9.0 time range.
Match and Index...
I still don't quite understand that one but I'll look back on my previous help notes from you to see what I can find and hopefully make heads and/or tails of it

Thank you.

ladyck3
aka: Laurie

 

[lionelhill]

I think what you'd really like is a button that takes the current situation, and puts it on the bottom of the table, with this month's date? That's probably a vba thing.
It's certainly easy enough with formulas to look up the appropriate score for any particular AHT. I'd worry about both the score and the AHT being entered manually, because what's to stop someone entering a score that doesn't match the AHT value they've entered.

But this is the useful bit: why don't you do your spreadsheet upside down, so the most recent entry is at the top? You can add a new line easily enough, and there's no scrolling needed. Assuming you have a date column, it's very easy to reverse the order as appropriate, every time someone wants it the other way up (just by sorting by date).

 

[ladyck3]

Hi lionelhill

No there is no date column, the top part where the data is entered and is erased every month to enter the new information of each month. That is why the table below, that will be static and has to be re-entered each month, scrolling up and down to repeat the data. There is a "Month" column so I guess that would suffice for your date mention but this is tryly a wonky report.

This is the reason I'm trying to make it easier... so I can enter the data each month and it will replicate in the static table below. I figure once it is replicated, I can copy/paste special/values so that it will not change for the month just completed.

I thank you for the advice but I really can't change the format as its standard across the board for all scorecards not just this one. Folks would bawk about the change I believe.

Again, I do thank you though for the input.

ladyck3
aka: Laurie

 

[SkipVought]

Here's a typical method for such periodic (like weekly or monthly) reporting.

Lookup Table to get a Score for any given AHT

[b]
Score  AHT[/b]
10      7
9       7.5
8       8
7       8.5
6       9
5       9.5
4      10
3      10.5
2      11
1      11.5
0

Apply Named range Using Names in TOP row

Data Input Table to be maintained from now forward..

[b]
Dte        ActAHT  ActScr[/b]
1/5/2013    8.1    [b]=INDEX(Score,MATCH(A2,AHT,1),1)[/b]
1/15/2013   7.4    10
2/6/2013    9.8     5
2/18/2013  10.3     4
3/4/2013   11.9     1
3/7/2013    7.6     9

Summary REPORT

[b]
Mon       AvgScore[/b]
Jan-2013  [b]=SUMPRODUCT((Dte>=A2)*(Dte<A3)*(ActScr))/SUMPRODUCT((Dte>=A2)*(Dte<A3))[/b]
Feb-2013     4.5
Mar-2013     5
Apr-2013

This, of course, assumes that you calculate the AVERAGE SCORE each period. Another aggregation is possible.

Also the formula is copy/pasted down, and assumes that each table is on a separate sheet beginning in A1.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

I don't have an understanding of some of that but I think its the assumption of the report itself... this is a bit more detailed than I think is necessary though.
<shrug>

ladyck3
aka: Laurie

 

[SkipVought]

in the ranges that are arranged in ASCENDING order, like C16
[tt]
=INDEX($A$5:$B$15,MATCH(MAX(D515),C5:C15,1),1)
[/tt]

in DESCENDING order, like G16
[tt]
=INDEX($A$5:$B$15,MATCH(MAX(H5:H15),G5:G15,-1),1)
[/tt]
will display the SCORE for the MAX value in the entry range in row 16.

As far as getting THAT valus into your monthly table, that would take some VBA.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

here's a possibility the uses a circular reference, and you also have to set Excel Options > Formulas > Calculation Options > Enable iterative calculation.

Then use this formula...
[tt]
C20: =IF(TEXT(TODAY(),"mmm")=$B20,C$16,C20)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

Ok, thanks.... pc is down and I'm home, will pursue on Sunday. As always. Thanks for your assistance.

ladyck3
aka: Laurie