0

Through copying other formulas/some help on this forum/blagging it I have managed to write a formula for a report I've create,d that basically takes the start date and the duration of the line entry and then returns the number 0.33 in separate month columns that fall between the start date and the end date.

Depending on the category of the line entry I also need some of the entries to return 0.2 for the relevant months instead of 0.33. I've managed to change the relevant parts of the formula to do this so can manually copy that formula to the relevant cells but ideally I would like to combine these both into one formula which can be applied to the whole sheet to make it as automated as possible. I've tried combining these myself but everything I have tried returns an error.

The two different formulas are:

=IF($L2=1,IF(AND(AH$1>=DATE(YEAR($N2),MONTH($N2),1),AH$1<=DATE(YEAR($N2),MONTH($N2)+($O2-1),1)),0.33,""),"")

=IF($L2=3,IF(AND(AH$1>=DATE(YEAR($N2),MONTH($N2),1),AH$1<=DATE(YEAR($N2),MONTH($N2)+($O2-1),1)),0.2,""),"")

Basically if the number in Column L is 1 I want the relevant month columns to return 0.33 and if the number in Column L is 3 I want the relevant month columns to return 0.2. If the number is not 1 or 3 then I want the month cells to be blank.

Please can you help me combine these IF statements?

Let me know if you need any more information.

1 Answer 1

0

A simple approach would be use the SWITCH function.

It looks like that you're using $L2 as a switch depending on whenever it's 1 or 3. Without digging into your formula the following should work:

=SWITCH($L2,1,IF(AND(AH$1>=DATE(YEAR($N2),MONTH($N2),1),AH$1<=DATE(YEAR($N2),MONTH($N2)+($O2-1),1)),0.33,""),3,IF(AND(AH$1>=DATE(YEAR($N2),MONTH($N2),1),AH$1<=DATE(YEAR($N2),MONTH($N2)+($O2-1),1)),0.2,""))

Note that I didn't test it and is just a conditional to run either IF statement. There might be a better way to combine them.

The more classical approach is to just nest the if statements. You could read it as follows: If it is not 1 check if it is 3 and if it is not 3 do nothing.

=IF($L2=1,IF(AND(AH$1>=DATE(YEAR($N2),MONTH($N2),1),AH$1<=DATE(YEAR($N2),MONTH($N2)+($O2-1),1)),0.33,""),IF($L2=3,IF(AND(AH$1>=DATE(YEAR($N2),MONTH($N2),1),AH$1<=DATE(YEAR($N2),MONTH($N2)+($O2-1),1)),0.2,""),""))
4
  • According to that page, SWITCH only applies to Office 2016. This function may not work in Office 2013 and earlier.
    – LPChip
    May 16, 2017 at 10:09
  • Did you specify a version anywhere? Otherwise you could also just combine the if statements. See the edit.
    – Seth
    May 16, 2017 at 10:11
  • Hi Seth, the combined IF statements in your edit works perfectly... thank you very much!! I did try the SWITCH version but even though I'm using Excel 2016 I don't seem to have that function, it just came up with the NAME error.
    – JH86
    May 16, 2017 at 10:51
  • Depending on your locale you would have to localize the function name. Though I'd wonder if IF would work in that case. Otherwise not sure why it would or wouldn't be available.
    – Seth
    May 16, 2017 at 11:26

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .