Fluctuating status related to Date

**Spellbound** · 11-04-2008, 03:38 PM

Hi everyone

I need to create a formula that will calculate the status of a customer based upon an input code and the relevent date.

The attached workbook is very simplified but should help.

Sheet1 contains the date of input (col A) for the client (col B) plus a code in column C to help ascertain the initial status of the a/c.

In Sheet2 (col B) I need a formula or possibly a VBA solution that will create a commission status for clients with 'N' in column C of Sheet1 which is current for 8 weeks after the input date in Sheet1. Thereafter this status then need to show say an 'S' for standard commission.

This result will be linked to the date shown in B2 on Sheet2. Just to make it clear, I only need a letter as the result to trigger off the actual commission calculations, which I have already done.

I hope that I have explained it clearly, if not, let me know.

TIA ...spellbound

**DonkeyOte** · 11-04-2008, 06:56 PM

I suspect what you want to achieve will be "relatively" straightforward (everything is relative!) but I think we need some clarification regards what constitutes "current" -- you imply it's tied to the data in Sheet2 but it's not immediately obvious what you mean.

I would suggest that you re-post your sample file with the expected results filled in and where sensible add a note as to why the result should be as you enter it -- ie S -- because N date no longer valid etc...

This will help us understand your logic and in turn you will get a pretty quick response... possibly not from me as I'm on UK time but a few people have looked at this already I think so will no doubt revisit later today.

**Spellbound** · 11-04-2008, 10:53 PM

Point taken, although I am not sure it is "relatively" straightforward!

I have revised the example workbook as attached, with notes on each worksheet, that should help to clarify the problem.

There is a great deal more data on the actual worksheets that I use but I have trimmed this right down to show only the relevent columns.

TIA ...spellbound

**DonkeyOte** · 11-05-2008, 05:00 AM

To avoid replicating expensive formulae I would actually advocate the use of one helper cell per row of data in your "Results" tab... this is to hold the row position of the last valid entry value for a given ID.

eg using your example file:

D6 on Results tab entered as array using SHIFT + CTRL + ENTER:

Please Login or Register  to view this content.

Copy the above down for all rows - eg using your example file to row 38.

You can now use this row position repeatedly in other formulae without needing to recompute the array each time you need that value.

To return the correct stat code in C

C6 on Results tab:

Please Login or Register  to view this content.

Copy the above down to C38.

You should find the results mirror your own values specified in B.

Hope that helps.

**Spellbound** · 11-05-2008, 06:02 AM

Hi DonkeyOte

Thanks for quick response, I will try to evaluate your solution today; if not will be at the weekend.

Let you know.

BTW, I am in UK as well but often have to these things at odd hours.

spellbound

**Spellbound** · 11-05-2008, 07:57 AM

Managed to do some quick testing and the combination of formulas appear to work fine in the examples that I tried.

However, when applied retrospectively, it produces erroneous results.

By that, I mean, if the date of the Results worksheet (B2) is prior to the date on the Input worksheet, it still shows N in column C of the Results worksheet.

The following information may or may not help with solving this.

The main workbook runs for 1 year, covering 52 weeks of the year from January to December.
Once data is entered on the Input worksheet, it is only likely to change in the event of a mistake or error in the entry.
The ID list (col A) of the Results worksheet may vary in length and content each week. It is dependent upon the ID numbers in a different worksheet.
It is possible to have an ID with an N status for different dates, although there should be at least 6 months difference between the 2 dates.

To test further, I have attached a workbook with a longer list of data together with the results of the formulas from DonkeyOte.

TIA ...spellbound

**DonkeyOte** · 11-05-2008, 08:03 AM

sorry just to clarify... you've highlighted row 119 on results tab which I presume means you believe this should be S rather than N, is that correct ?

the value in D says that the last entry for that ID either on or before the date in B2 is on row 86 on Input sheet -- input!D86 is N ... so why should this be S ?

**Spellbound** · 11-05-2008, 08:23 AM

No problem, it is not the easiest of problems to explain.

I highlighted that ID as an example because it should only show as N from 11/10/08 to 29/11/08 (8 weeks). Prior to that it may or may not exist on the Input worksheet but the list of ID's on the Results worksheet is drawn from a different worksheet.

The date of 4/10/08 for that particular ID reflects a data entry point in order to create an opening balance for week 11/10/08 on another worksheet. This is not the ideal way of doing things but I have to work with a company whose IT is a bit archaic, to say the least.

The N code is also used to trigger other events but the S code can be used at all other times because the adjustable commission scale will simply produce a Zero result.

Hope this clarifies the situation better.

**DonkeyOte** · 11-05-2008, 08:26 AM

So the key thing here is how you handle dates in A.... are you now saying that every date on Input!A should be offset by 7 days as it only comes into effect the following week ?

If not I'm afraid I'm completely baffled...

**Spellbound** · 11-05-2008, 08:54 AM

Not quite, any entry with an S status in the Input worksheet remains as S status in the Results worksheet.

Any entry in the Input worksheet with an N status, needs to remain as N status for 8 weeks in the Results worksheet, commencing 1 week after the date of entry in the Input worksheet.

e.g. 955155 is entered as N on the 4/10/08 in the Input worksheet and should only be N in the Results worksheet for weeks 11/10/08 to 29/10/08.

If this ID appears in the Results worksheet, where the date in B2 is before or after 11/10/08 to 29/10/08; then it is a workable solution to use the S status for dates outside of these 8 weeks.

Sorry to have caused so much confusion...

**DonkeyOte** · 11-05-2008, 10:22 AM

so would this work for you:

D6 as array:

Please Login or Register  to view this content.

copied down

C6:

Please Login or Register  to view this content.

**Gearcutter** · 11-05-2008, 10:33 AM

Hi Guys can I add my twopennorth?

see the attachment as the results page does not show the answer too clearly I have coppied the input page to page three. Change the weekending date to see how things change

Regards Howard

**Spellbound** · 11-05-2008, 11:43 AM

Hi Howard

Always willing to look at alternatives, I think it helps all of us to see which solutions work best for a particular scenario but unable to try it out today.

Will update here when I have done some testing.

Thanks ...spellbound

**Spellbound** · 11-05-2008, 11:50 AM

Hi DonkeyOte

Managed to do a quick test on your formulas but these gave the reverse result in that the N Status started at the right week but did not change to the S status until a week after the correct week.

I did some juggling and came up with the following, using your original array formula with the amended formula below:

Please Login or Register  to view this content.

This appears to work on the specific (highlighted) example that I used in my last test workbook but I have not had enough time to check out in more detail. I welcome your comments good or bad on this formula.

Thanks for your help ...spellbound

**Spellbound** · 11-10-2008, 08:07 AM

Ignore this post - mixed up my workbooks

**Spellbound** · 11-10-2008, 02:01 PM

Despite my earlier post (now cancelled) I thought that I had this problem solved.

Still unable to get the precise results that I require although I have been playing around with the formulas for most of the day.

I seem to be getting erroneous results where the row number appears as zero on the Results worksheet. See example on the Results worksheet.

I have updated the Test workbook to show the required results for the N status dates. Althought this is shown in the Input worksheet, these are the dates at which the relevent ID should show as N in column B of the Results worksheet.

Hope this helps ...spellbound

**Spellbound** · 11-11-2008, 06:20 AM

Made a fresh start on this problem this morning and came up with a possible solution by adding a third argument to the formula in B6 (copied down).

Please Login or Register  to view this content.

It appears to work in all of the combinations that I have tried but could not find a simple solution to error checking the results, apart from doing it manually. Revised workbook attached.

Any comments (good or bad) welcome, also any other ideas, especially if it can be done as a single formula without a helper column.

spellbound

**DonkeyOte** · 11-11-2008, 06:30 AM

EDIT: This bears no relation to your recent post but is a resolution of prior post issues. Does not require any additional cells... and should work based on my interpretation of your criteria.

I would first suggest you alter your INDEX array formula in Results C6 onwards to be:

Please Login or Register  to view this content.

The above will return the last valid index position for each ID ... for ex if the ID record has status of N it will add 7 days to the date in A before checking it's validity against date set in B2 on results tab... using your example data, row 119 for ID 955155 was previously returning index position of 87 but given the date on this entry is 4/10/2008 - ie the same as B2 -- and the fact that the status is N this is an invalid record... the above revised formula would return index position of 2 as this is the last *valid* record <= Results B2 date.

The formula you have in B needs to be adjusted to account for those records where INDEX position returned is 0 -- where 0 it therefore follows there is no valid record and thus result must be S -- I would therefore advise you use the following formula in Results B6 onwards.. you can also remove one of your tests

Please Login or Register  to view this content.

You could use an OR (re: OR(C6=0,INDEX(...)="S"),"S") but then you're doing the INDEX even when not required (ie C = 0)

I hope the above resolve your issues.

**Spellbound** · 11-11-2008, 11:51 AM

Thanks for the latest formulas, will try them out later when my system is back up and running properly

spellbound

Fluctuating status related to Date

LinkBack

Thread Tools

Rate This Thread

Display

Fluctuating status related to Date

Additional Information

The End Result

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions