Formula Help

[Glasgowsmile]

Good morning,

Looking for help with the formula below:

=IFERROR(IF(AND($P12<>"", $S12<>"",$J12-$G12>$P12),$S12*$P12,""),"")

The problem I have is that sometimes J12 is a larger number than G12 so it throws off the comparison against P12.

For example:
J12 = 73
G12 = 44

So it comes out to -29, but I want it to be just 29. How do I remove the negative component from this?

 

[Sufiyan97]

What are the value in P12 and S12?

 

[Glasgowsmile]

What are the value in P12 and S12?

As an example:
P12 = 30
S12 = 100

but really the issue is coming from this:

$J12-$G12>$P12

due to the negative numbers it will sometimes provide.

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=IFERROR(IF(AND($P12<>"", $S12<>"",ABS($J12-$G12)>$P12),$S12*$P12,""),"")

 

[Glasgowsmile]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=IFERROR(IF(AND($P12<>"", $S12<>"",ABS($J12-$G12)>$P12),$S12*$P12,""),"")

Ah thanks, this works.

An additional question on something like the formula above... eventually the number that gets put into P12 will be > than the J12-G12 and so it will be blank. How do I make it hold the previous value that did work instead of being blank?

 

[Fluff]

You can't