Product if cell is a certain number in already huge formula

[darrow87]

Hey guys. Sorry to drop this huge formula on you and ask this question but I figured it was worth a shot. If I can ask this a better way please let me know.

I have the huge formula below and I have bolded the relevant portions.

=IF($E33 > Q$1, "Billing has not started", IF(AND(Q$2-$F33>0, NOT(ISBLANK($F33))), "Closed", IF(SUM(DATEDIF($E33,Q$1,"M"),1)>36, "No further billing", IF($B33="GAL", PRODUCT(VLOOKUP(SUM(DATEDIF($E33,Q$1, "M"),1), '2nd Circuit Billing Outline'!$A$2:$C$73, 2, TRUE), 2/3), VLOOKUP(SUM(DATEDIF($E33,Q$1,"M"),1), '2nd Circuit Billing Outline'!$A$2:$C$73, 3, TRUE)*(2/3)))))

Basically, what I need is to only multiply the result of my vlookups by 2/3 if the result of the vlookup is NOT 2500. For some reason I've gotten this far and now can't figure out how to do this step.

Any help would be much appreciated.

 

[jasonb75]

That's a small formula compared to some that we get asked to 'fix'.

The way you had your formula structured, it would have needed 4 vlookups to check and return the result, by moving a few things around I've cut it back to 2 (would have been 1 without checking if the result was 2500).

=IF($E33>Q$1,"Billing has not started",IF(AND(Q$2-$F33>0,$F33>0),"Closed",IF(DATEDIF($E33,Q$1,"M")>35,"No further billing",
PRODUCT(VLOOKUP(DATEDIF($E33,Q$1,"M")+1,'2nd Circuit Billing Outline'!$A$2:$C$73,IF($B33="GAL",2,3)),IF(VLOOKUP(DATEDIF($E33,Q$1,"M")+1,'2nd Circuit Billing Outline'!$A$2:$C$73,IF($B33="GAL",2,3))=2500,1,2/3)))))

Not tested the formula in excel, hopefully there are no errors in it.

 

[darrow87]

Haha, ok gotcha. Thanks very much for this response. Unfortunately this doesn't seem to be working for me. It basically skews my timelines for where the billing and text ("closed" etc) needs to be populated. They are just appearing in the wrong spots. I'm going to try and post an example/describe more about what I need to happen.

Main Sheet
7/31/2019 8/31/2019 (etc)
Billing type Date of Appointment Date Closed 7/1/2019. 8/1/2019 (etc)
GAL 5/7/2016 6/9/2020. Billing # or text populated here
LC 7/15/2018 4/4/2018. Billing # or text populated here

Second Circuit Billing Outline

Month # GAL. LC
1 2500 0
2 0 600
(etc)

So I calculate a number using the DATEDIF M between the billing month and the date of appointment (I have two billing month date formats listed - 7/31/2019 and 7/1/2019 - because that helped me get the numbers working in a certain way for returning both the billing amount and whether it was closed, there are some nuances there). I then vlookup that number against the month # column in the other sheet and get me the right billing number.

The last two months of the period need to be prorated unless they are 2,500. I didn't realize the 2500 exception until the end and and now I am struggling to add it in to what I already have.

I hope this is helpful. Thank you again for any help you can provide.

 

[jasonb75]

It's kinda hard to follow a description with a typed example, all of the formatting gets lost.

If you click the XL2BB button on the reply toolbar you will find instructions to download and install an excel add-in which you can use to post (fictional) samples from your sheet straight to the board, preserving any formatting, formulas etc. making it much easier for people to see what you are trying to do.