# IF's

#### Silvermini63

##### Active Member
Um I know this isn’t quite right but what I am trying to do is put a fail safe in

Basically if the last bit is what I am having trouble with if f4 is greater than L4 (can’t remember which way the sign is to go) put in L4’s value minus 1 cent. That after all of the if at the beginning is looked at

=IF(ISBLANK(B4),"",IF(G4=0,D4/(100%-\$I\$2),D4/(100%-G4),IF(f4>L4,L4-.01,f4))))

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Think this might be what your looking for,

=IF(ISBLANK(B4),"",IF(G4=0,OR(D4/(100%-\$I\$2),(D4/(100%-G4)),(F4>L4)),L4-0.01))

not entirely sure what you were asking for though

edit:

It looks as though you are trying to get two results back from one formula in a single cell, is this what you are trying to do?

No it didn't work than you very much for your help.....

this is from a sheet that it has a base % mark up across the board but then if an individual % mark-up is entered it needs to take that and the last bit is saying if F4 which is the sell price is equal or greater than L4 which is our top end price return the top end price minus one cent…..hope this makes it a bit clearer.

What result are you looking for?
Which cell is this formula in?
can you give example numbers with result you want?

Ok

D4 is the cost Reference and this case is worth 11.03
G4 is the individual Markup % in this case 40%
F4 is the calculated sell which will be \$18.38 (due to G4)
I2 is the Blanket markup 5 in this case 20%
L4 is our top end price of \$17.53

So basically I want it to do the first part of the formula giving me \$18.38 but due to it being equal or higher than the top end price (L4) I wan it to return L4 minus 1 cent leaving me with \$17.53

But if say the Markup in G4 was only 10% giving me \$12.26 than is the number to be displayed due to it being under L4

Hope this is clear enough for you…. Thank again for your help

You must have IF statements inside each other.

tested this and think it works as you want it to

=IF(ISBLANK(B4),"",IF(IF(G4=0,D4/(100%-I2),D4/(100%-G4))>L4,L4-0.01,IF(G4=0,D4/(100%-I2),D4/(100%-G4))))

If you notice it does dot include references to F4 from what information you have given me it seems that F4 is only =IF(G4=0,D4/(100%-I2),D4/(100%-G4) so it seems to work without it being refered to.

thank you very much i have tried it and it seem to work very well i would not have got my head around that one

Replies
10
Views
582
Replies
2
Views
331
Replies
2
Views
265
Replies
3
Views
280
Replies
3
Views
434
Legacy 143009
L

1,217,346
Messages
6,136,043
Members
449,981
Latest member
kjd513

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back