Writing Nested If formula which returns "0" once condition is met

[Dgetske]

Hello,

I have a nested if statement to return values once a certain number of units are deployed. What I'm having trouble with is how to structure the formula so that is returns zero once the condition is met. So for instance, the first time units deployed hits the 23-28 range, then return (G29/3) but return 0 if units are in the range after that. I tried to do this with the beginning if statement (AE29>1,0...) but its not working consistently.

=IF(AE29>1,0,IF(AND(AF7>=48,AF7<=50),$G$29/3,IF(AND(AF7>=23,AF7<=28),$G$29/3,IF(AND(AF7>=1,AF7<=3),$G$29/3,0))))

Any suggestions?

Thanks!

 

[JackDanIce]

Welcome to the board. The logic in your IF statement is difficult to follow so guess only, try:

=--(AE29<=1)*($G$29/3)*(MIN(1,--(AF7=MEDIAN(AF7,1,3))+--(AF7=MEDIAN(AF7,23,28))+--(AF7=MEDIAN(AF7,48,50))))

 

[DanteAmor]

Your formula says if AE29 is greater than 1 then return 0.

If in AE29 you have 0 or 1, then check the conditions
AF7> = 48, AF7 <= 50
AF7> = 23, AF7 <= 28
AF7> = 1, AF7 <= 3
If any meets then
$G$29 / 3


But if in cell AF7 you have for example 100, then it returns 0.

According to that logic, where do you have the problem?
You can comment on what values ​​you have in AE29 and in AF7 and what result you expect.

 

[Dgetske]

Hi there, thanks for the suggestion but it didn't seem to correct the issue. Maybe if you understood my objective, that would help. I basically need a formula for incremental capex which occurs once the first unit is deployed, after the 23rd unit is deployed and upon the 48th unit being deployed. So the capital is deployed in 1/3 increments. Where I'm getting tripped up is trying to build a condition which returns 0 once the threshold units are deployed, so I don't have capex values repeating once the threshold is met. Does that make sense?

 

[Dgetske]

Hi and Thanks!

I modified the formula a bit so here is an example of the formula in AI29 which returns $20,000 when I want it to return 0 because the threshold is met in AF29 where AF7=>23,AF7=<28. I know it is doing this because AH29 is blank. I can't seem to find a consistent way of returning zero once a threshold is met. Suggestions?

=IF(AH29=($G$29/3),0,IF(AND(AI7>=48,AI7<=50),$G$29/3,IF(AND(AI7>=23,AI7<=28),$G$29/3,IF(AND(AI7>=1,AI7<=3),$G$29/3,0))))

Your formula says if AE29 is greater than 1 then return 0.

If in AE29 you have 0 or 1, then check the conditions
AF7> = 48, AF7 <= 50
AF7> = 23, AF7 <= 28
AF7> = 1, AF7 <= 3
If any meets then
$G$29 / 3


But if in cell AF7 you have for example 100, then it returns 0.

According to that logic, where do you have the problem?
You can comment on what values ​​you have in AE29 and in AF7 and what result you expect.

 

[JackDanIce]

Pretend I haven't got a clue what capex means or any of the explanation.

Also acknowledge I can't see your screen from here nor read your mind.

Use simple address (e.g. A1, B1, C1) and state exact values and expected outcome, in other words a working and non working example of every condition you are testing

To break down following:

=--(AE29<=1)*($G$29/3)*(MIN(1,--(AF7=MEDIAN(AF7,1,3))+--(AF7=MEDIAN(AF7,23,28))+--(AF7=MEDIAN(AF7,48,50))))

--(AE29<=1) returns 1 if AE29 is below 1 and 0 if AE29 is greater than or equal to 1

*($G$29/3) a scalling factor x 1 or 0 (see first part)

--(AF7=MEDIAN(AF7,1,3))+--(AF7=MEDIAN(AF7,23,28))+--(AF7=MEDIAN(AF7,48,50))))

Returns 0 IF AF7 is not in range 1-3, 23-28 or 48-50 otherwise returns 1 if AF7 is found in any range
MIN sets a ceiling of 1 (i.e. if it is found in a range) - this is not needed actually

So your final formula is {IS AE29 < 1} * scalling_factor * {IS AF7 found in a specified range}

Where each IS part returns 1 or 0. Anything times 0 is 0

 

[JackDanIce]

Without MIN try:

=--(AE29<=1)*($G$29/3)*(--(AF7=MEDIAN(AF7,1,3))+--(AF7=MEDIAN(AF7,23,28))+--(AF7=MEDIAN(AF7,48,50)))

 

[DanteAmor]

You could explain with your words what data you have in the cells, what data you want to compare and what results you expect.

Use real examples


That is, if cell AE29 = ?? and in cell AF = ?? so ...

Try to put several cases of what you can have and what you expect of result.