Excel - Trying to return a outcome based on value.

willhub · 31 Mar 2022 at 10:30

I'm really struggling with this one.

I'm trying to create a formula that can return an outcome based on the value of another cell.

So I have a table with a list of plans, each plan have a minimum payment option (these minimum payments change based on the value of balances typed in).

I have another box that specifies yes or no if a contractual payment is affordable or not, as if it's not affordable the type of plan that should appear would be different.

So if Yes and payment is less than minimum payment, then specify this plan, if yes and payment is more or equal to this other minimum payment, then specify this plan, if No and payment is more than this minimum payment, and Yes for change in circumstances in the next 12 months, specify this payment.

I have tried IF And formulas, IF formulars and CHOOSE formulas.

I'm struggling to get it to work

for example "=IF(G22="YES",CHOOSE((J22>D15)+(J22>=D16)+ (AND(J22> 0, J22<D15)),"COL","PTP","Term Ext))"

Where "term ext" should be displayed, it just won't do it, it just shows "COL", I can never get it to display more than two of the outcomes, if it will it usually just comes up with VALUE.

lsg1r · 1 Apr 2022 at 14:05

No expert on this (I can do very basic excel formulas lol) but could it be the = sign in the bit (j22>=D16) that's throwing it out.

It just seems a little out of place compared with the rest of the formula

iamtheoneneo · 1 Apr 2022 at 14:13

is this not an array? It reads like an array formula not a normal formula but hard without seeing the sheet.

Greboth · 1 Apr 2022 at 22:33

Could you not just use nested IF statements so IF(G22="Yes","COL",IF(G22="No","PTP"....

Obviously you'd need to put the necessary and statements in etc.

lord filbuster · 2 Apr 2022 at 18:45

willhub said:
for example "=IF(G22="YES",CHOOSE((J22>D15)+(J22>=D16)+ (AND(J22> 0, J22<D15))

Aren't these two bits mutually exclusive? Your index value is never going to be higher than 2, so it will never choose 'Term Ext', because only two of the arguments can be true at the same time. It will show #value when all three are false as well.

Also, I don't think you have an outcome for when G22 = No unless I'm reading it wrong.

Could you share the sheet or say which cell is which, it's quite hard to follow otherwise.