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.
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,""),""))
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments