trying to figure out what type of formula to use

nmss18

Active Member
Hello,
In the table below, I have a list of clients and the number of license fees they bought into per month. As soon as I see a value I know that is the first month that client signed up. If there are blank values following any cell with a number, I know that client was deactivated.
So in the table below, I see that Client 2 was added in 2014 June, and client 10 was deactivated after 2013 December.
In cell B6, I need to formulate a formula that just tells me simply if this client was added in this period or not. So in cell B7 next to client 2, I would like to see "ADDED", and next to client 10 in cell B15, I would like to see "LOST". The rest of th cells, I would like to leave blank.
I am thinking that it should be some sort of LOOKUP formula but am not sure.

If anyone, could help, I would appreciate it.
Thanks,
nmss18

( I am using a free online HTML maker, so the quality isnt so great)
 Fiscal Month End User Current User Status 2013 July 2013 August 2013 September 2013 October 2013 November 2013 December 2014 January 2014 February 2014 March 2014 April 2014 May 2014 June 2014 July 2014 August 2014 September Client 1 Active 11 11 11 11 11 11 11 11 11 11 12 12 13 13 12 Client 2 Active 1 1 1 1 Client 3 Active 3 3 4 4 4 4 4 5 5 5 5 5 5 5 5 Client 4 Active 4 4 4 4 4 4 4 4 4 4 3 4 4 4 3 Client 5 Active 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 Client 6 Active 6 6 6 6 6 6 6 6 6 6 6 6 6 6 6 Client 7 Active 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 Client 8 Active 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 Client 9 Active 6 6 6 6 6 6 6 6 6 6 5 5 5 5 5 Client 10 Inactive 5 5 5 3 3 3 Client 11 Active 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5

<tbody>
</tbody>

Last edited:

Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
I'm assuming that you have no completely blank rows. In which case you only need to test the beginning and end of the rows so something like this will tell you if they have been added, lost or added then lost:
I just copied your table into excel and put this formula in column S, feel free to switch the cell references around.

That's really amazing. It works. But why? What is the logic behind this?

Also, there will never be completely blank rows but this wil be a dynamically updating table and the time range may change.
what would you do in this case?
Thanks!

For a dynamically changing table I would have two entry cells above the table where you put the start of your time range in B1, for example, and the end time in C2. (You can setup a formula for it to take the last occupied time range in the row, but for now lets just test the formula below.) Then have a hlookup for these times with an extended table like so:
=if(hlookup(\$b\$1, \$d\$4:\$aa\$100, a4, false)= "","Added","")&if(hlookup(\$c\$1, \$d\$4:\$aa\$100, a4, false)="","Lost","")
Where d4:aa100 is your data table, column a has a list of numbers that reference the row in the hlookup, so then a3 = 1 and a4 = 2 etc. You may have to tweak the cell references to adjust for where your data is.
Then as you add rows and columns you won't need to adjust the formula until you reach the max (max row being 100 and max column being aa) then you can either extend the hlookups or if you insert the row/column, the hlookup should extend by itself.

I haven't checked this solution since I'm on a train replying from a phone. But hopefully it will work.

I will check this out. Thanks for your help.

Replies
5
Views
227
Replies
1
Views
117
Replies
10
Views
222
Replies
2
Views
140
Replies
6
Views
174

1,220,980
Messages
6,157,185
Members
451,404
Latest member
Probe

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.

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

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