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>)