excel 2010: nested 'if' as the 'value if:false' argument

[mischge]

hi all

here's the situation:

a) if(client limit>x;if(utilization>client limit;calculation1;0))
b) if(client limit<x;if(utilization>x;calculation2;0))

i've tried to nest b) into a), in place of the value if: false (0) argument without success.
i dont have any VBA skills, so would prefer to use a formula, if possible.

thanks for any input!</x;if(utilization>

 

[mischge]

== please ignore first post, something went wrong==

hi all

here's the situation:

A) if(client limit>x;if(utilization>client limit;calculation1;0))
b) if(client limit<<x;if(utilization>x;if(utilization>x;calculation2;0))

i've tried to nest b) into a), in place of the value if: False (0) argument without success.
I dont have any vba skills, so would prefer to use a formula, if possible.

Thanks for any input!</x;if(utilization>

 

[Nuclear Wessels]

hi all

here's the situation:

a) if(client limit>x;if(utilization>client limit;calculation1;0))
b) if(client limit<x;if(utilization>x;calculation2;0))

i've tried to nest b) into a), in place of the value if: false (0) argument without success.
i dont have any VBA skills, so would prefer to use a formula, if possible.

thanks for any input!</x;if(utilization>

Hi Mischge

Think this should be what you are looking for:

<x,if(utilization> if(client limit>x,if(utilization>client limit,calculation1,if(client limit<<x,if(utilization>x,calculation2,""),"")),"")

Corrected for your new values</x,if(utilization></x,if(utilization>

 

[CharlesChuckieCharles]

Looks like

=IF(AND(ClientLimit<X,utilization>X),Calc2,IF(AND(ClientLimit>X,utilization>ClientLimit),CalC1,0))

 

[mischge]

hi nuclear wessels, charleschuckiecharles

thank you both for your quick replies.
both your solutions dont work, because both calc1 and calc2 are dependent on 2 different conditions each.

A) if(client limit>x;if(utilization>client limit;calculation1;0))
b) if(client limit<<x;if(utilization style="color: rgb(51, 51, 51); font-style: italic; background-color: rgb(242, 246, 248);">x;if(utilization>x;calculation2;0))

my initial approach was:
</x;if(utilization>if(client limit>x;if(utilization>client limit;calculation1;if(client limit<<x;if(utilization style="color: rgb(51, 51, 51); font-style: italic; background-color: rgb(242, 246, 248);">x;if(utilization>x;calculation2;0)</x;if(utilization>)))
where the underlined part is the'false' statement of the initial 'if' function.

but excel returns a true/false value.

any further suggestions?

 

[mischge]

sorry, charleschuckiecharles, i re-visited your approach and it works!

thanks a lot, i've been sweating over this a while now and just couldnt get it right.

cheers!

 

[Nuclear Wessels]

Ok, ive revised it again based off your update to the first post.

Try:

<x,if(utilization><x,if(utilization><x,if(utilization>if(client limit>x,if(utilization>client limit,calculation1,if(client limit<<x,if(utilization>x,if(utilization>x,calculation2,""),"")),"")

You realize though that in the first part of the statement you check Client Limit against X and then utilization against client limit.
In the second part of the statement you check Client Limit against X again but this time its utilization against x not client limit?</x,if(utilization></x,if(utilization></x,if(utilization></x,if(utilization>

 

[CharlesChuckieCharles]

Looks like

=IF(AND(ClientLimit<x,utilization>X),Calc2,IF(AND(ClientLimit>X,utilization>ClientLimit),CalC1,0))

Thats odd because i did an edit and pasted and saved but this appears to be wrong

Should be =IF(ClientLimit=X,Calc2,IF(AND(ClientLimit>X,utilization>ClientLimit),CalC1,0))</x,utilization>

 

[mischge]

@nuclear wessel: the logic of your formula is correct, but i have to adjust the closing parentheses. once i do that, i get true/false values.

@CCC: i didnt copy/past the formula, but the syntax idea works. i'll let you know if i get fired because risk management didnt work