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

[desibouy]

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,""))

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.

 

[desibouy]

Wow Thanks Man,


See this sample sheet -

Mega File Upload - Sample Sheet.xlsx

Thanks again

Can't believe I can't edit my own posts however just to iterate and sorry for creating loads of posts.

I am trying to find the correct selling price using the right freight.
to figure out selling price you do cost price divide .68 (Caluclation)

1. if item has 13 in Freight column then to the end of Calulation.

2. If price is 6.95 and below $50 then $3.00 else $6.95

3. if M2 (UOM) has "CA" and has incontinence in Matrix Parent only then add $6.95.

Thanks Again

 

[jtakw]

Wow Thanks Man,


See this sample sheet -

Mega File Upload - Sample Sheet.xlsx

Thanks again

Can you upload your file to another filesharing site, like maybe Dropbox, or something else, Mega wants me to register before I can download your file, and I'm not going to register just to do that.

 

[desibouy]

Hi,

Try this please

https://www.dropbox.com/s/op9kpmvw8vyqu8b/Sample Sheet.xlsx?dl=0

 

[jtakw]

Can't believe I can't edit my own posts however just to iterate and sorry for creating loads of posts.

I am trying to find the correct selling price using the right freight.
to figure out selling price you do cost price divide .68 (Caluclation)

1. if item has 13 in Freight column then to the end of Calulation.

2. If price is 6.95 and below $50 then $3.00 else $6.95

3. if M2 (UOM) has "CA" and has incontinence in Matrix Parent only then add $6.95.
Thanks Again

Is M2 (UOM) Column M with header "MAP", and Matrix Parent Column C in your sample sheet?

 

[desibouy]

Is M2 (UOM) Column M with header "MAP", and Matrix Parent Column C in your sample sheet?

I deleted some fields which weren't relevant however I've just uploaded another file. All fields on there now.

Sorry man

 

[desibouy]

Ignore Column C5. That's wrong.

Matrix Type is Column F.

M2 is UOM.

S2 is Column type.

Is there a chat thing on here which would make it easy to talk?

 

[jtakw]

Not entirely sure if I understand what you mean, but try this and see if it works for you.

 =IF(AND(M2="CA",ISNUMBER(SEARCH("INCONTINENCE",S2)),ISNUMBER(SEARCH("PARENT",F2))),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)))

And no, there's no chat on this forum.

 

[desibouy]

Not entirely sure if I understand what you mean, but try this and see if it works for you.

 =IF(AND(M2="CA",ISNUMBER(SEARCH("INCONTINENCE",S2)),ISNUMBER(SEARCH("PARENT",F2))),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)))

And no, there's no chat on this forum.

DUDE this is AWESOME! Works Great Thank You, Thank You Thank You Absolute Life Saver.

 

[desibouy]

Hi Jtakw,

When I randomly checked it all looked well and it worked. However looking closely it's not working but kinda is. For some reason the 3rd Rule/Condition doesn't work -- "if M2 (UOM) has "CA" and contains the word incontinence in the Matrix Parent Row then add $6.95". Ignoring the 2nd condition but not the 1st condition.

I've re-uploaded the file in Dropbox and also added a Column in there showing the correct price for those Cases. It's only missing like $3.00


When I say it's kinda working, it's because anything above $50 the 6.95 is working fine but I guess this is because of the 2nd Condition - If cost price / .68 and and is below $50 then $3.00 else $6.95.


Does that make sense?




https://www.dropbox.com/s/op9kpmvw8vyqu8b/Sample%20Sheet.xlsx?dl=0

 

[jtakw]

OK, two ways to fix this:

First way:

Excel 2010
	F	G	M	S	JD	JE	JH
1	Matrix Type	Sub Item Of	UOM	Site Category 1	Inventory Purchase Price	Selling Price	Freight
2	Parent Matrix Item			Incontinence Supplies : Underwear	10.68	18.99	6.95
3	Child Matrix Item	Protection Plus Classic Protective Underwear	CA	Incontinence Supplies : Underwear	32	54.49	6.95
4	Child Matrix Item	Protection Plus Classic Protective Underwear	BG	Incontinence Supplies : Underwear	11.82	20.49	6.95
5	Child Matrix Item	Protection Plus Classic Protective Underwear	CA	Incontinence Supplies : Underwear	32.28	54.49	6.95
6	Child Matrix Item	Protection Plus Classic Protective Underwear	BG	Incontinence Supplies : Underwear	10.76	18.99	6.95
7	Child Matrix Item	Protection Plus Classic Protective Underwear	CA	Incontinence Supplies : Underwear	31	52.99	6.95
8	Child Matrix Item	Protection Plus Classic Protective Underwear	BG	Incontinence Supplies : Underwear	10.68	18.99	6.95
9	Child Matrix Item	Protection Plus Classic Protective Underwear	CA	Incontinence Supplies : Underwear	30	51.49	6.95
10	Child Matrix Item	Protection Plus Classic Protective Underwear	BG	Incontinence Supplies : Underwear	10.68	18.99	6.95
11	Child Matrix Item	Protection Plus Classic Protective Underwear	CA	Incontinence Supplies : Underwear	32	54.49	6.95
12	Child Matrix Item	Protection Plus Classic Protective Underwear	BG	Incontinence Supplies : Underwear	11.7	20.49	6.95
13	Parent Matrix Item
14	Child Matrix Item	Deluxe 3-in-1 Bedside Steel Commode
15	Child Matrix Item	Deluxe 3-in-1 Bedside Steel Commode
Sheet1

Cell Formulas
Range	Formula
S3	=IF(G3<>"",S2,"")
JE2	=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)))

Copy S3 formula down Column S, copy JE2 formula down Column JE.
However, if you want to preserve the appearance of your sheet, then do it this way:

Excel 2010
	F	G	M	S	T	JE	JF	JI
1	Matrix Type	Sub Item Of	UOM	Site Category 1		Inventory Purchase Price	Selling Price	Freight
2	Parent Matrix Item			Incontinence Supplies : Underwear	Incontinence Supplies : Underwear	10.68	18.99	6.95
3	Child Matrix Item	Protection Plus Classic Protective Underwear	CA		Incontinence Supplies : Underwear	32		6.95
4	Child Matrix Item	Protection Plus Classic Protective Underwear	BG			11.82		6.95
5	Child Matrix Item	Protection Plus Classic Protective Underwear	CA			32.28		6.95
6	Child Matrix Item	Protection Plus Classic Protective Underwear	BG			10.76		6.95
7	Child Matrix Item	Protection Plus Classic Protective Underwear	CA			31		6.95
8	Child Matrix Item	Protection Plus Classic Protective Underwear	BG			10.68		6.95
9	Child Matrix Item	Protection Plus Classic Protective Underwear	CA			30		6.95
10	Child Matrix Item	Protection Plus Classic Protective Underwear	BG			10.68		6.95
11	Child Matrix Item	Protection Plus Classic Protective Underwear	CA			32		6.95
12	Child Matrix Item	Protection Plus Classic Protective Underwear	BG			11.7		6.95
13	Parent Matrix Item
14	Child Matrix Item	Deluxe 3-in-1 Bedside Steel Commode
15	Child Matrix Item	Deluxe 3-in-1 Bedside Steel Commode
16					Hide this Column
Sheet1

Cell Formulas
Range	Formula
T2	=S2
T3	=IF(G3<>"",T2,"")
JF2	=IF(AND(M2="CA",ISNUMBER(SEARCH("INCONTINENCE",T2))),CEILING(JE2/0.68+6.95,0.5)-0.01,IF(JI2=13,CEILING(JE2/0.68+13,0.5)-0.01,IF(AND(JI2=6.95,(JE2/0.68)<50),CEILING(JE2/0.68+3,0.5)-0.01,CEILING(JE2/0.68+6.95,0.5)-0.01)))

Insert a new Column after Column S, new Column will be T, enter formula in T2, enter formula in T3 and copy down, hide Column T, enter formula in JF and copy down.