finding the last 3 months in a table

H

hg090589

New Member
Joined
Sep 18, 2018
Messages
11
Hello,

I have a table with months I and various figures for each month.

I am wanting to display in columns O the last 3 months that have data in column D (completed).
For Example below August, September and October should have a 1,2,3 in column O

This is what I have but it won't work.
=IF(AND(D4>0,ISBLANK(D5)),1,IF(O5=1,2,IF(O5=2,3,””)))


[TABLE="width: 715"]
<colgroup><col width="119" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4352;"> <col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"> <col width="57" style="width: 43pt; mso-width-source: userset; mso-width-alt: 2084;"> <col width="64" style="width: 48pt;" span="9"> <col width="66" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2413;"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 119, bgcolor: transparent"][/TD]
[TD="width: 256, bgcolor: #FFFF66, colspan: 4"]
[/TD]
[TD="width: 256, bgcolor: #D9D9D9, colspan: 4"] [/TD]
[TD="width: 258, bgcolor: #FFFF99, colspan: 4"] [/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: #D9D9D9"]Month[/TD]
[TD="width: 71, bgcolor: #FFFF66"]Tasks Scheduled by Pirana[/TD]
[TD="width: 57, bgcolor: #FFFF66"]Completed Pirana Tasks[/TD]
[TD="width: 64, bgcolor: #FFFF66"]Incomplete Pirana Tasks[/TD]
[TD="width: 64, bgcolor: #FFFF66"]Completion Rate[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Tasks Scheduled by Pirana[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Completed Pirana Tasks[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Incomplete Pirana Tasks[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Completion rate[/TD]
[TD="width: 64, bgcolor: #FFFF66"]Tasks Scheduled by Pirana[/TD]
[TD="width: 64, bgcolor: #FFFF66"]Completed Pirana Tasks[/TD]
[TD="width: 64, bgcolor: #FFFF66"]Incomplete Pirana Tasks[/TD]
[TD="width: 66, bgcolor: #FFFF66"]Completion Rate[/TD]
[TD="bgcolor: transparent"]last 3 momths[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]July 2018[/TD]
[TD="bgcolor: #FFFF99, align: right"]1513[/TD]
[TD="bgcolor: #FFFF99, align: right"]266[/TD]
[TD="bgcolor: #FFFF99, align: right"]1246[/TD]
[TD="bgcolor: #FFFF99, align: right"]18%[/TD]
[TD="bgcolor: #F2F2F2, align: right"]246[/TD]
[TD="bgcolor: #F2F2F2, align: right"]44[/TD]
[TD="bgcolor: #F2F2F2, align: right"]202[/TD]
[TD="bgcolor: #F2F2F2, align: right"]18%[/TD]
[TD="bgcolor: #FFFF99, align: right"]35[/TD]
[TD="bgcolor: #FFFF99, align: right"]17[/TD]
[TD="bgcolor: #FFFF99, align: right"]18[/TD]
[TD="bgcolor: #FFFF99, align: right"]49%[/TD]
[TD="bgcolor: transparent, align: center"]#NAME?[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]August 2018[/TD]
[TD="bgcolor: #FFFF99, align: right"]1079[/TD]
[TD="bgcolor: #FFFF99, align: right"]139[/TD]
[TD="bgcolor: #FFFF99, align: right"]940[/TD]
[TD="bgcolor: #FFFF99, align: right"]13%[/TD]
[TD="bgcolor: #F2F2F2, align: right"]1155[/TD]
[TD="bgcolor: #F2F2F2, align: right"]119[/TD]
[TD="bgcolor: #F2F2F2, align: right"]435[/TD]
[TD="bgcolor: #F2F2F2, align: right"]10%[/TD]
[TD="bgcolor: #FFFF99, align: right"]50[/TD]
[TD="bgcolor: #FFFF99, align: right"]13[/TD]
[TD="bgcolor: #FFFF99, align: right"]37[/TD]
[TD="bgcolor: #FFFF99, align: right"]26%[/TD]
[TD="bgcolor: transparent, align: center"]#NAME?[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]September 2018[/TD]
[TD="bgcolor: #FFFF99, align: right"]1312[/TD]
[TD="bgcolor: #FFFF99, align: right"]306[/TD]
[TD="bgcolor: #FFFF99, align: right"]1005[/TD]
[TD="bgcolor: #FFFF99, align: right"]23%[/TD]
[TD="bgcolor: #F2F2F2, align: right"]294[/TD]
[TD="bgcolor: #F2F2F2, align: right"]228[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]78%[/TD]
[TD="bgcolor: #FFFF99, align: right"]28[/TD]
[TD="bgcolor: #FFFF99, align: right"]6[/TD]
[TD="bgcolor: #FFFF99, align: right"]22[/TD]
[TD="bgcolor: #FFFF99, align: right"]21%[/TD]
[TD="bgcolor: transparent, align: center"]#NAME?[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]October 2018[/TD]
[TD="bgcolor: #FFFF99, align: right"]1898[/TD]
[TD="bgcolor: #FFFF99, align: right"]309[/TD]
[TD="bgcolor: #FFFF99, align: right"]1588[/TD]
[TD="bgcolor: #FFFF99, align: right"]16%[/TD]
[TD="bgcolor: #F2F2F2, align: right"]226[/TD]
[TD="bgcolor: #F2F2F2, align: right"]37[/TD]
[TD="bgcolor: #F2F2F2, align: right"]185[/TD]
[TD="bgcolor: #F2F2F2, align: right"]16%[/TD]
[TD="bgcolor: #FFFF99, align: right"]34[/TD]
[TD="bgcolor: #FFFF99, align: right"]8[/TD]
[TD="bgcolor: #FFFF99, align: right"]26[/TD]
[TD="bgcolor: #FFFF99, align: right"]24%[/TD]
[TD="bgcolor: transparent, align: center"]#NAME?[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]November 2018[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: center"]#DIV/0![/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: center"]#DIV/0![/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: center"]#DIV/0![/TD]
[TD="bgcolor: transparent, align: center"]#NAME?[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]December 2018[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: center"]#DIV/0![/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: center"]#DIV/0![/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: center"]#DIV/0![/TD]
[TD="bgcolor: transparent, align: center"]#NAME?[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]January 2019[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: center"]#DIV/0![/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: center"]#DIV/0![/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: center"]#DIV/0![/TD]
[TD="bgcolor: transparent, align: center"]#NAME?[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]February 2019[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: center"]#DIV/0![/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: center"]#DIV/0![/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: center"]#DIV/0![/TD]
[TD="bgcolor: transparent, align: center"]#NAME?[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]March 2019[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: center"]#DIV/0![/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: center"]#DIV/0![/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: center"]#DIV/0![/TD]
[TD="bgcolor: transparent, align: center"]#NAME?[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]April 2019[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: center"]#DIV/0![/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: center"]#DIV/0![/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: center"]#DIV/0![/TD]
[TD="bgcolor: transparent, align: center"]#NAME?[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]May 2019[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: center"]#DIV/0![/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: center"]#DIV/0![/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: center"]#DIV/0![/TD]
[TD="bgcolor: transparent, align: center"]#NAME?[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]June 2019[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: center"]#DIV/0![/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: center"]#DIV/0![/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: center"]#DIV/0![/TD]
[TD="bgcolor: transparent, align: center"]#NAME?[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]July 2019[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: center"]#DIV/0![/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: center"]#DIV/0![/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: center"]#DIV/0![/TD]
[TD="bgcolor: transparent, align: center"]#NAME?[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]August 2019[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: center"]#DIV/0![/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: center"]#DIV/0![/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: center"]#DIV/0![/TD]
[TD="bgcolor: transparent, align: center"]#NAME?[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]September 2019[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: center"]#DIV/0![/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: center"]#DIV/0![/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: center"]#DIV/0![/TD]
[TD="bgcolor: transparent, align: center"]#NAME?[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]October 2019[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: center"]#DIV/0![/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: center"]#DIV/0![/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: center"]#DIV/0![/TD]
[TD="bgcolor: transparent, align: center"]#NAME?[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]November 2019[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: center"]#DIV/0![/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: center"]#DIV/0![/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: center"]#DIV/0![/TD]
[TD="bgcolor: transparent, align: center"]#NAME?[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]December 2019[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: center"]#DIV/0![/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: right"]0[/TD]
[TD="bgcolor: #F2F2F2, align: center"]#DIV/0![/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: right"]0[/TD]
[TD="bgcolor: #FFFF99, align: center"]#DIV/0![/TD]
[TD="bgcolor: transparent, align: center"]#NAME?[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Re: finding the last 3 momths in a table

The quotes are the wrong type they should be " not ”
also you're testing if D5 is blank, but from your post it never will be.
 
Upvote 0
Re: finding the last 3 momths in a table

Fluff said:
The quotes are the wrong type they should be " not ”
also you're testing if D5 is blank, but from your post it never will be.
Click to expand...

Hi thanks for replying.

that quotes are right in my spreadsheet it’s just wrong on here for some reason.

Also i realised it is looking for blank, do you know how i would amend that formula to say greater anything but 0 instead of isblank


thanks for your help
 
Upvote 0
Re: finding the last 3 momths in a table

do you know how i would amend that formula to say greater anything but 0 instead of isblank
Click to expand...
I am afraid that part in red is not very clear.

Do you want greater than zero like this:
Code: 
D5>0
or equal to zero like this?
Code: 
D5=0
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
645
smozgur
smozgur
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
MrDB4Excel
  • Question Question
If Find Blank Cell
Replies
0
Views
710
MrDB4Excel
MrDB4Excel
A
  • Solved
Calendar issue
Replies
1
Views
2K
AndyEd
A
S
  • Solved
Linking to a workbook that doesn't yet exist - for use in template creating
2
Replies
11
Views
2K
SophieYaldwyn
S

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top