Help! Excel Dummy

J

Joey Shabbadoo

New member
Joined
Feb 27, 2017
Messages
4
Reaction score
0
Points
0
Hey,

I am relatively new to excel and have been tasked with something I am now massively stuck on.

I need to nest three formulas in to one cell. Ideally I need the first option to add 120 days to the date in B7, then 90 then 30.

This is what I have...

=IF(COUNTIF(C5,">0")-COUNTIF(C5,">9"),(B5+120),""),OR(IF(COUNTIF(C5,">10")-COUNTIF(C5,">15"),(B5+90),""),OR(IF(COUNTIF(C5,">16")-COUNTIF(C5,">25"),(B5+30),"")

the first formula seems to be ok on its own but I cant work out the rest.

Can anyone help???
 
Something like

=C5+LOOKUP(C5,{0,9,15,25},{120,90,30,0})
 
Hi Pecoflyer,

Thank you for responding. I am not quite sure what your formula does (although it seems impressive to me whatever :caked:)

I have attached a sample spreadsheet as it might help explain what I am trying to do...

I need the formula to look up any value between 16-25 and return a date that is 30 days ahead of the date inserted in cell B5.

So if A2 is 06/01/2017 and B2 is 20, the formula should return 05/02/2017 in F5.

Does that make sense?
 

Attachments

  • Sample Spreadsheet.xlsx
    11.8 KB · Views: 11
In H2 enter =A2+LOOKUP(B2,{0,9,15,25},{120,90,30,0}) and pull down as needed
 
Change it to =A2+LOOKUP(B2,{0,9,16,26},{120,90,30,0})
 
or cheat:
=A2+LOOKUP(B2+0.1,{0,9,15,25},{150,90,30,0})
 
Thank yooooooooouuuuuu!!!! :first:

I am amazed by this one, I never would have got it.

Can you explain the formula and how it works so I can try and understand it for next time please
 
You must log in or register to reply here.
Back
Top