Excel Table Help

[Adam Miller]

I have created a table that displays the weekdays of the month, who has the
highest commission and how much they earnt in the form:

Date | Seller| Commission | Day of the Week
01/06/09 | XX | £YY | Monday
02/06/09 | ZZ | etc....

There is also a box on the sheet near the top where you select todays date
(which is used for an entirely different function.)

The table is used for management purposes only, but a shorter version will
be made public to the sales team. This needs to display the last 5 week - ie,
working - days and the top seller and commission for that day. This will take
a similar form:

Day | Seller | Commission
Monday | X | £
Tuesday | X | £
Wednesday | X | £
Thursday | X | £
Friday | X | £

Although it wont necessarily be in that order. as it is the last 5 working
days it may be: thurs, fri, mon, tues, weds; or whatever the case may be.

I am not entirely sure about the best foruma to use to create this table. My
thought is to use a VLOOKUP on the "todays date" box (mentioned earlier) to
look for the preceding weekdays date, 2 precedinging weekdays and so forth,
on the initial table and displaye the corresponding day, seller and
commission data. My main problem is working it so it only uses weekdays.

Any suggestions? Thanks

 

[Bernie Deitrick]

Adam,

Let's say that your table is in columns A to D, with the headers in row 5.
In cell E6, array enter (enter using Ctrl-Shift-Enter) the formula

=IF(AND(MAX(($A$6:$A$1000=A6)*$C$6:$C$1000)=C6,(MAX($A$6:$A$1000)-6)<=A6),"Show
Me!","")

and copy down to match (if you have more than 1000 rows of data, change the
3 instances of 1000 to match your row count)

Then filter your table using column E, showing only "Show Me!"

HTH,
Bernie
MS Excel MVP

 

[Herbert Seidenberg]

Excel 2007 PivotTable
Show last 5 working days.
User friendly formula.
http://www.mediafire.com/file/dywb0tgzdjk/06_08_09.xlsx