Dec 30 2020 03:50 PM
HI GUYS, so I have three different IF statements that I want to string together and I have looked everywhere and can't find an example quite like mine. Also when I do string my formulas together it says"inconsistent formula"
so just a quick description: I am making a cashflow forecast and charging interest on my closing balance of the cash flow. it states that the interests are 1% for positive cashflow balance, -1.6% for negative balance up to 2.5m OVERDRAft limit and if it goes over that amount then it is charged at 3.6%.
the formulae I have come up with all the research is =IF(D23>-3401031.57, D23*3.6%, IF(D23>-2500000, D23*1.3%,IF(D23>0,D23*1%))). the -3,401,031.57 is the lowest figure in the cashflow hence I have used that as the stop figure.
I am a newbie btw guys so please bear that in mind. and thanks for any suggestion and answers in advance.
Dec 30 2020 04:21 PM
@nawango I WAS ABLE TO COMEUP WITH MY OWN ANSWER AHA.
IT WAS =IF(C25<-2500000,C25*3.6%,IF(C25<0,C25*1.3%,C25*1%))
COMPLICATED IT MUCH MORE THAN I NEEDED TO TBH
Dec 30 2020 04:21 PM
SolutionCan you share your workbook or a sample file that doesn't have confidential info? At the moment, it seems the issue is regarding the D23 figure you are using for you NESTED IF statement:
=IF(D23>-3401031.57, D23*3.6%,
IF(D23>-2500000, D23*1.3%,
IF(D23>0,D23*1%)))
At the moment, your D23 is referenced as a number that will always be satisfied by the first condition, else a FALSE will be returned.
Dec 30 2020 04:27 PM
@adversi OFC I CAN:)
Dec 30 2020 04:28 PM
@adversi I KINDA FIGURED IT OUT BUT WANNA HEAR FROM A SECOND OPINION OF WHETHER IT LOOKS RIGHT. JUST STARTED TODAY AND IS DUE ON THE SECOND ;P.
Dec 30 2020 04:40 PM
You did a good job figuring it out. IF statement makes sense and is scalable to be reused for future updates
Dec 30 2020 04:21 PM
SolutionCan you share your workbook or a sample file that doesn't have confidential info? At the moment, it seems the issue is regarding the D23 figure you are using for you NESTED IF statement:
=IF(D23>-3401031.57, D23*3.6%,
IF(D23>-2500000, D23*1.3%,
IF(D23>0,D23*1%)))
At the moment, your D23 is referenced as a number that will always be satisfied by the first condition, else a FALSE will be returned.