HELP!!! I am working on a nested IF formula that just doesn't work, any help is appreciated.

Elaine Byman

I am trying to calculate the total revenue per month as well as the Margin % per month from our Sales Pipeline. Some of the projects run into the following month so we need to see what the revenue and margin are for each part of that time frame.

I was able to get the formula to work for the margin % but only for one cell reference and the start/end dates fall within July.  I need to calculate all data within the row and to calculate what % is July and what would be Aug for those projects that run into the next month.

The formula that worked for the single cell reference looks like this =IF(MONTH([Start Date]12) = 7, IF(MONTH([End Date]12) = 7, Percent12))

 

What I think it should look like is this =IF(MONTH([Start Date]:[Start Date]) = 7, IF(MONTH([End Date]:[End Date]) = 7, Percent:Percent))  However when this is entered I get an error (#Missing or invalid parameters)

Any help is appreciated!

 

Greg Gates

Hello Elaine!

 

You're receiving that error because the MONTH function can only take a single cell as input. I don't think I fully understand your situation, but could you maybe try comparing the the months of the start and end dates, and then change your calculations if those months are different? For example,

 

IF (MONTH([Start Date]1) <> MONTH([End Date]1), <calculate percent for different months here>, <calculate percent for same month here>)

Elaine Byman

Thank you, I can give that a try and see what I get.

Elaine Byman

Greg I tried your suggestion however I am not certain I know what information should be entered in <calculate percent for different months here>, <calculate percent for same month here>).  Are you referring to the column and row location of where I want that data to appear?  If so I did try that and it didn't work either.

Would I be able to share my sheet with you so you can see what I'm trying to acheive and understand the issue I have with the formula?

Greg Gates

Sure, we could try that! My email is greg.gates@innotecgroup.com