IF's

Silvermini63

Active Member
Joined
Sep 25, 2006
Messages
293
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

Please help….

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

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

JamieDuncan

Board Regular
Joined
Aug 23, 2006
Messages
132
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?
 

Silvermini63

Active Member
Joined
Sep 25, 2006
Messages
293
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.
 

JamieDuncan

Board Regular
Joined
Aug 23, 2006
Messages
132
What result are you looking for?
Which cell is this formula in?
can you give example numbers with result you want?
 

Silvermini63

Active Member
Joined
Sep 25, 2006
Messages
293

ADVERTISEMENT

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
 

JamieDuncan

Board Regular
Joined
Aug 23, 2006
Messages
132
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.
 

Silvermini63

Active Member
Joined
Sep 25, 2006
Messages
293
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 :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,133,454
Messages
5,658,868
Members
418,475
Latest member
ExcelBeginner233

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top