Formula help...Please!

[Sheriffd]

I am trying to construct a spreadsheet to help a colleague keep track of his workloads. I have done pretty well so far... but now I am stuck on two things.

Any help would be really appreciated.

The first thing I want to do is...

A formula which looks in column O for "certain text". then all of the rows that contain that text, look in Column T for a number. Then work out the average of all of the numbers that fit the two criteria.

Then...
(I'm not sure if this is possible)

Look in column O for the "certain text" and display the rows where the date in column Q is within the last 7 days.

Again thanks in advance!

 

[crook_101]

Hi & Welcome to the Board,

First bit is, assuming you have headers, in an empty column:

=if(02="certain text",T2,"")

Please not that you will need the "" marks around what ever "certain text" you are looking for - eg "apples".

You won't get excel to display just the rows where "certain text" is automatically, but you can either apply a filter or use some sort of conditional formatting incorprating "=tooday()" which you can then look against the dates in column Q.

Hope this helps??

Ian

 

[Sheriffd]

Hi & Welcome to the Board,

"=tooday()" which you can then look against the dates in column Q.

Ian

Thanks Ian,

the
=today()
part... what does that do? Would i put -7 or something?

Thanks again!

 

[crook_101]

Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>Dates</TD><TD style="TEXT-ALIGN: right">01/03/2011</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">24/01/2011</TD><TD>due</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">25/01/2011</TD><TD>due</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">26/01/2011</TD><TD>due</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">27/01/2011</TD><TD>due</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">28/01/2011</TD><TD>due</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">29/01/2011</TD><TD>due</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">30/01/2011</TD><TD></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">31/01/2011</TD><TD></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">01/02/2011</TD><TD></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">02/02/2011</TD><TD></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: right">03/02/2011</TD><TD></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: right">04/02/2011</TD><TD></TD></TR></TBODY></TABLE>

Sheet1​

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B1</TH><TD style="TEXT-ALIGN: left">=TODAY()</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B2</TH><TD style="TEXT-ALIGN: left">=IF($B$1-A2>30,"due","")</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B3</TH><TD style="TEXT-ALIGN: left">=IF($B$1-A3>30,"due","")</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B4</TH><TD style="TEXT-ALIGN: left">=IF($B$1-A4>30,"due","")</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B5</TH><TD style="TEXT-ALIGN: left">=IF($B$1-A5>30,"due","")</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B6</TH><TD style="TEXT-ALIGN: left">=IF($B$1-A6>30,"due","")</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B7</TH><TD style="TEXT-ALIGN: left">=IF($B$1-A7>30,"due","")</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B8</TH><TD style="TEXT-ALIGN: left">=IF($B$1-A8>30,"due","")</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B9</TH><TD style="TEXT-ALIGN: left">=IF($B$1-A9>30,"due","")</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B10</TH><TD style="TEXT-ALIGN: left">=IF($B$1-A10>30,"due","")</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B11</TH><TD style="TEXT-ALIGN: left">=IF($B$1-A11>30,"due","")</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B12</TH><TD style="TEXT-ALIGN: left">=IF($B$1-A12>30,"due","")</TD></TR><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B13</TH><TD style="TEXT-ALIGN: left">=IF($B$1-A13>30,"due","")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
Hi, sorry got carried away with submitting before I typed....

Yeah you can either use the today function in either conditional formatting of add a column, as above, to generate a "Due" date using simple formula - you can the CF the cell/row to highlight whatever colour to show it is due.

HTH and apologies for the fragmented post!!

Ian

 

[Sheriffd]

That's worked a treat... well, ish.

The formula does what I want for the jobs that are dated within the last 7 days. Unfortunately, where there is no date it returns a value error.

Sorry to be a pain... BUT is there any way I can stop this?

Thank you thank you thank you!

 

[crook_101]

Hi,

Yeah this is no problem, just use the following modified formula and copy down:

=IF(A2="","",IF($B$1-A2>30,"due",""))

Ammend the number after the ">" for the number of days you want.

Hope this helps??

Ian,