Need help with a formula.

  • Thread starter Thread starter Steve Cohen
  • Start date Start date
S

Steve Cohen

I'm looking for a formula that will do the following...

If the conditional formating in C2(=All_World_Activity>=All_World_Bonus*5)
is true it will display the contents of F4(which can also be a cell named
All_World_Total).

If the conditional formating in C2(=All_World_Activity>=All_World_Bonus*5)
is false it will see if F4 (or All_World_Total) - Trans_type!F6 (or
All_World_Bonus) is greater than F4 (or All_World_Total).

If it is it will display =F4-Trans_type!F6 (or
=All_World_Total-All_World_Bonus).

If the above F4 (or All_World_Total) - Trans_type!F6 (or All_World_Bonus) is
<= or equal to F4 (or All_World_Total) is will display 0.

I have a couple of thing that don't seem to work quite right and I can't
combine them.
=IF((All_World_Total-All_World_Bonus<=All_World_Total),"0",(All_World_Total)
)
=IF(C2=TRUE,(All_World_Total),(All_World_Total-All_World_Bonus)).

Thanks;

Steve
 
Unfortunately, you can't access the conditional formattings Truth
value directly. You can use the same formulae however. If I'm
parsing your statements correctly:

IF AWA>=AWB*5 Then AWT
IF AWA<AWB*5 Then If ((AWT-AWB) > AWT) Then (AWT-AWB) Else 0

The second line can be simplified to

IF AWA<AWB*5 Then If AWB<0 Then (AWT-AWB) Else 0

Combining and putting into an IF() statment:

=IF(AWA>=AWB*5, AWT, IF(AWB<0,(AWT-AWB),0))
 
Hi Steve,

You can't use conditional formatting as an argument in a
formula. Conditional formatting is used to define the
display characteristics of a cell and has no effect on the
actual value of that cell.

This is my best guess at what you want:

=IF(ACTIVITY>=BONUS*5,TOTAL,IF(TOTAL-BONUS>TOTAL,TOTAL-
BONUS,0))

I would suggest that you use more compact names when you
use defined names. This makes things much easier to read.

Biff
 
What you suggested is just about right, I was probably not clear in what I
wnated it to do, or I wasn't sure myself.

The only addition that I need is
IF(All_World_Total-All_World_Bonus>All_World_Total,All_World_Total-All_World
_Bonus,0)) is false then instead of it dispalying 0 I would like it to
display the value if it is a positive # and 0 if it is <=0.
 
Hi Steve,

I think just a simple change in the criteria will work:

=IF(ACTIVITY>=BONUS*5,TOTAL,IF(TOTAL-BONUS>=0,TOTAL-
BONUS,0))

Biff
 
Back
Top