IF Formula not working - Trying to Search, AND and Round up.

D

desibouy

Board Regular
Joined
Nov 20, 2014
Messages
98
Office Version
  1. 365
Platform
  1. Windows
Hi,

I'm having problems trying to do this formula and it just doesn't want to work :( Please can anyone help me?



=IF(JH2="13",CEILING(JD2/0.68+13,0.5)-0.01,""),IF(AND(JH2="6.95",(JD2/0.68))<50,CEILING(JD2/0.68+3,0.5)-0.01,CEILING(JD2/0.68+6.95,0.5)-0.01),IF(AND(M2="CA",ISNUMBER(SEARCH(S2,"INCONTINENCE"))),CEILING(JD2/0.68+6.95,0.5)-0.01,""))
Click to expand...


Basically it reads,

IF Freight price is 13 THEN to Divide the Cost price by .68 and ADD the 13.

IF Freight price is 6.95 AND the Cost Price Divide .68 is LESS than $50 THEN add $3.00 ELSE ADD 6.95.

IF M2 (which is Unit of Measurement) has CA AND Column S2 (which is the category) contains the word "Incontinence" THEN calculate Cost Price Divide .68 and add 6.95 Regardless.

Everything is rounded up.





P.S- forgive me if the title is wrong.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
Code: 
[COLOR=#333333][I]=IF(JH2="13",CEILING(JD2/0.68+13,0.5)-0.01,""),IF(AND(JH2="6.95",(JD2/0.68))<50,CEILING(JD2/0.68+3,0.5)-0.01,CEILING(JD2/0.68+6.95,0.5)-0.01),IF(AND(M2="CA",ISNUMBER(SEARCH(S2,"INCONTINENCE"))),CEILING(JD2/0.68+6.95,0.5)-0.01,""))[/I][/COLOR]

Apologies. Hope I used the Right Tag around it.
 
Upvote 0
desibouy said:
Code: 
[COLOR=#333333][I]=IF(JH2="13",CEILING(JD2/0.68+13,0.5)-0.01,""),IF(AND(JH2="6.95",(JD2/0.68))<50,CEILING(JD2/0.68+3,0.5)-0.01,CEILING(JD2/0.68+6.95,0.5)-0.01),IF(AND(M2="CA",ISNUMBER(SEARCH(S2,"INCONTINENCE"))),CEILING(JD2/0.68+6.95,0.5)-0.01,""))[/I][/COLOR]

Apologies. Hope I used the Right Tag around it.
Click to expand...


Bump anyone please?
 
Upvote 0
desibouy said:
Hi,

I'm having problems trying to do this formula and it just doesn't want to work :( Please can anyone help me?






Basically it reads,

IF Freight price is 13 THEN to Divide the Cost price by .68 and ADD the 13.

IF Freight price is 6.95 AND the Cost Price Divide .68 is LESS than $50 THEN add $3.00 ELSE ADD 6.95.

IF M2 (which is Unit of Measurement) has CA AND Column S2 (which is the category) contains the word "Incontinence" THEN calculate Cost Price Divide .68 and add 6.95 Regardless.

Everything is rounded up.





P.S- forgive me if the title is wrong.
Click to expand...

Hi,

Above in red, when you say REGARDLESS, do you mean ignoring the first 2 conditions?
 
Upvote 0
jtakw said:
Hi,

Above in red, when you say REGARDLESS, do you mean ignoring the first 2 conditions?
Click to expand...

No, I should have explained it a bit better. Regardless as in the Selling price being less than or greater than 50. That's the only condition it should ignore.
 
Upvote 0
desibouy said:
No, I should have explained it a bit better. Regardless as in the Selling price being less than or greater than 50. That's the only condition it should ignore.
Click to expand...

Isn't this formula to figure out the Selling price?, if not, what cell is the selling price in?
 
Upvote 0
desibouy said:
No, I should have explained it a bit better. Regardless as in the Selling price being less than or greater than 50. That's the only condition it should ignore.
Click to expand...


Also just realized --

These items I'm trying to add is on a spreadsheet which is populated with Parent Child items. The Incontinence bit is only available for Parent Rows. Child items do not have the categories as they belong to the Patent. So how can I get it to work for all the child items under the Parent Code?

[TABLE="width: 1281"]
<tbody>[TR]
[TD]Protection Plus Classic Protective Underwear[/TD]
[TD]Parent Matrix Item[/TD]
[TD][/TD]
[TD]Incontinence Supplies : Underwear[/TD]
[/TR]
[TR]
[TD]MSC23000[/TD]
[TD]Child Matrix Item[/TD]
[TD]Protection Plus Classic Protective Underwear[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MSC23000H[/TD]
[TD]Child Matrix Item[/TD]
[TD]Protection Plus Classic Protective Underwear[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Parent Matrix Item[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
I have 3 versions of the formula based on your OP:

Code: 
=IF(JH2=13,CEILING(JD2/0.68+13,0.5)-0.01,IF(AND(JH2=6.95,(JD2/0.68)<50),CEILING(JD2/0.68+3,0.5)-0.01,IF(OR(JH2<>6.95,(JD2/0.68)>=50),CEILING(JD2/0.68+6.95,0.5)-0.01,IF(AND(M2="CA",ISNUMBER(SEARCH("INCONTINENCE",S2))),CEILING(JD2/0.68+6.95,0.5)-0.01,""))))
This one is basically a corrected version of your original formula.

Code: 
=IF(AND(M2="CA",ISNUMBER(SEARCH("INCONTINENCE",S2))),CEILING(JD2/0.68+6.95,0.5)-0.01,IF(JH2=13,CEILING(JD2/0.68+13,0.5)-0.01,IF(AND(JH2=6.95,(JD2/0.68)<50),CEILING(JD2/0.68+3,0.5)-0.01,CEILING(JD2/0.68+6.95,0.5)-0.01)))
This one is based on additional info you provided in post #5

Code: 
=IF(JH2=13,CEILING(JD2/0.68+13,0.5)-0.01,IF(AND(JH2=6.95,(JD2/0.68)<50),CEILING(JD2/0.68+3,0.5)-0.01,CEILING(JD2/0.68+6.95,0.5)-0.01))
This one is based on the logic of your explanation from OP and post #5, since "IF Freight price is 6.95 AND the Cost Price Divide .68 is LESS than $50 THEN add $3.00 ELSE ADD 6.95.

IF M2 (which is Unit of Measurement) has CA AND Column S2 (which is the category) contains the word "Incontinence" THEN calculate Cost Price Divide .68 and add 6.95 Regardless.
", you really don't need the extra tests in your original formula, because it'll end up being "CEILING(JD2/0.68+6.95,0.5)-0.01" if the first two tests fails anyway.

desibouy said:
Also just realized --

These items I'm trying to add is on a spreadsheet which is populated with Parent Child items. The Incontinence bit is only available for Parent Rows. Child items do not have the categories as they belong to the Patent. So how can I get it to work for all the child items under the Parent Code?

[TABLE="width: 1281"]
<tbody>[TR]
[TD]Protection Plus Classic Protective Underwear[/TD]
[TD]Parent Matrix Item[/TD]
[TD][/TD]
[TD]Incontinence Supplies : Underwear[/TD]
[/TR]
[TR]
[TD]MSC23000[/TD]
[TD]Child Matrix Item[/TD]
[TD]Protection Plus Classic Protective Underwear[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MSC23000H[/TD]
[TD]Child Matrix Item[/TD]
[TD]Protection Plus Classic Protective Underwear[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Parent Matrix Item[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

If you need help with formula for scenario above, I'm afraid you'll need to provide a sample table of your data, and describe in detail what you need the formula to do.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Easy Fix needed on Formula
Replies
3
Views
319
admiral100
A

Forum statistics

Threads
1,223,889
Messages
6,175,226
Members
452,620
Latest member
dsubash

Share this page

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
Back
Top