Merging two formulas

Hi,

If I understand the requirement I'd be inclined to use a lookup table.

Put the upper limit of each band in the first column c and in the second enter the text you want to return
Then in H2 use

Formula:

Please Login or Register  to view this content.

or....

=IF(E2="","",LOOKUP(ROUNDUP((IF(F2="",TODAY(),F2)-E2)/7,0),{1,2,8,12,13,26,39,52},{"1 wk","2 wks","8 wks","12 wks","13 - 26 wks","26 - 39 wks","39 - 52 wks","52+ wks"}))

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

Oh, and note that I changed 12-26 weeks to 13-26 weeks, as 12 weeks is already captured... You should do the same for the others, shouldn't you???

Originally Posted by Glenn Kennedy

Oh, and note that I changed 12-26 weeks to 13-26 weeks, as 12 weeks is already captured... You should do the same for the others, shouldn't you???

Yes seen that - only issue is it gives me a N/A for people seen on the same day. For these their wait time should come up one week.

And anyone waiting longer than a year is showing up as 39-52 in the wait time. Edit - that may have fixed itself.

Look back later. I'm out for a 6 mile walk!!

Well, you could adjust what Glenn gave you to cover less than a day with this addition (in red) …
=IF(E2="","",LOOKUP(ROUNDUP((IF(F2="",TODAY(),F2)-E2)/7,0),{0,1,2,8,12,13,26,39,52},{"less than 1 wk","1 wk","2 wks","8 wks","12 wks","13 - 26 wks","26 - 39 wks","39 - 52 wks","52+ wks"}))

Not seeing the second issue...

And anyone waiting longer than a year is showing up as 39-52 in the wait time.

unless by your edit you are saying it is resolved?

I removed the ROUNDUP (it was getting in the way...) and twiddled with it a bit... I think it covers all the bases now...

Originally Posted by Glenn Kennedy

Look back later. I'm out for a 6 mile walk!!

Shouldn't that be 9.6 Km Glen?

According to your original specification, it gives teh correct answer. So, over to you. Please draw up an ACCURATE list of the the cut-off times and phrases. After that, it's easy. I can't do that for you, though... as only YOU know what you want!!

and for 3 days to 1 week??

OK:

=IF(E2="","",LOOKUP((IF(F2="",TODAY(),F2)-E2),{0,4,14,56,84,168,252,364},{"0-3 d","1-2 wks","2-8 wks","8-12 wks","12-24 wks","24-39 wks","39-52 wks","52+ wks"}))