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.
 
Sort by date Sort by votes
jtakw said:
OK, two ways to fix this:

First way:

Excel 2010
FGMSJDJEJH
Matrix TypeSub Item OfUOMSite Category 1Inventory Purchase PriceSelling PriceFreight
Parent Matrix ItemIncontinence Supplies : Underwear10.6818.996.95
Child Matrix ItemProtection Plus Classic Protective UnderwearCAIncontinence Supplies : Underwear3254.496.95
Child Matrix ItemProtection Plus Classic Protective UnderwearBGIncontinence Supplies : Underwear11.8220.496.95
Child Matrix ItemProtection Plus Classic Protective UnderwearCAIncontinence Supplies : Underwear32.2854.496.95
Child Matrix ItemProtection Plus Classic Protective UnderwearBGIncontinence Supplies : Underwear10.7618.996.95
Child Matrix ItemProtection Plus Classic Protective UnderwearCAIncontinence Supplies : Underwear3152.996.95
Child Matrix ItemProtection Plus Classic Protective UnderwearBGIncontinence Supplies : Underwear10.6818.996.95
Child Matrix ItemProtection Plus Classic Protective UnderwearCAIncontinence Supplies : Underwear3051.496.95
Child Matrix ItemProtection Plus Classic Protective UnderwearBGIncontinence Supplies : Underwear10.6818.996.95
Child Matrix ItemProtection Plus Classic Protective UnderwearCAIncontinence Supplies : Underwear3254.496.95
Child Matrix ItemProtection Plus Classic Protective UnderwearBGIncontinence Supplies : Underwear11.720.496.95
Parent Matrix Item
Child Matrix ItemDeluxe 3-in-1 Bedside Steel Commode
Child Matrix ItemDeluxe 3-in-1 Bedside Steel Commode

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]S3[/TH]
[TD="align: left"]=IF(G3<>"",S2,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]JE2[/TH]
[TD="align: left"]=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)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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
FGMSTJEJFJI
Matrix TypeSub Item OfUOMSite Category 1Inventory Purchase PriceSelling PriceFreight
Parent Matrix ItemIncontinence Supplies : UnderwearIncontinence Supplies : Underwear10.6818.996.95
Child Matrix ItemProtection Plus Classic Protective UnderwearCAIncontinence Supplies : Underwear326.95
Child Matrix ItemProtection Plus Classic Protective UnderwearBG11.826.95
Child Matrix ItemProtection Plus Classic Protective UnderwearCA32.286.95
Child Matrix ItemProtection Plus Classic Protective UnderwearBG10.766.95
Child Matrix ItemProtection Plus Classic Protective UnderwearCA316.95
Child Matrix ItemProtection Plus Classic Protective UnderwearBG10.686.95
Child Matrix ItemProtection Plus Classic Protective UnderwearCA306.95
Child Matrix ItemProtection Plus Classic Protective UnderwearBG10.686.95
Child Matrix ItemProtection Plus Classic Protective UnderwearCA326.95
Child Matrix ItemProtection Plus Classic Protective UnderwearBG11.76.95
Parent Matrix Item
Child Matrix ItemDeluxe 3-in-1 Bedside Steel Commode
Child Matrix ItemDeluxe 3-in-1 Bedside Steel Commode
Hide this Column

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]T2[/TH]
[TD="align: left"]=S2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]T3[/TH]
[TD="align: left"]=IF(G3<>"",T2,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]JF2[/TH]
[TD="align: left"]=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)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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.
Click to expand...


Drag down Column T too and then hide it? As I'm trying to automate the whole spreadsheet to add products I didn't want manual work but I have no other choice atleast the pricing will be correct and this Incontinence categories would only be on a few products so shouldn't be much of an issue.

Thanks again, I'll keep you posted after I've tested this.
 
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Hi,

Sorry, this is a correction to the second method described in post #20, the previous formula for Column T won't work properly, use this one instead.


Excel 2010
FGMSTJEJFJI
1Matrix TypeSub Item OfUOMSite Category 1Inventory Purchase PriceSelling PriceFreight
2Parent Matrix ItemIncontinence Supplies : UnderwearIncontinence Supplies : Underwear10.6818.996.95
3Child Matrix ItemProtection Plus Classic Protective UnderwearCA326.95
4Child Matrix ItemProtection Plus Classic Protective UnderwearBG11.826.95
5Child Matrix ItemProtection Plus Classic Protective UnderwearCA32.286.95
6Child Matrix ItemProtection Plus Classic Protective UnderwearBG10.766.95
7Child Matrix ItemProtection Plus Classic Protective UnderwearCA316.95
8Child Matrix ItemProtection Plus Classic Protective UnderwearBG10.686.95
9Child Matrix ItemProtection Plus Classic Protective UnderwearCA306.95
10Child Matrix ItemProtection Plus Classic Protective UnderwearBG10.686.95
11Child Matrix ItemProtection Plus Classic Protective UnderwearCA326.95
12Child Matrix ItemProtection Plus Classic Protective UnderwearBG11.76.95
13Parent Matrix Item
14Child Matrix ItemDeluxe 3-in-1 Bedside Steel Commode
15Child Matrix ItemDeluxe 3-in-1 Bedside Steel Commode
16Hide this Column
Sheet1
Cell Formulas
RangeFormula
T2=IF(S2<>"",S2,IF(G2<>"",T1,""))
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)))


Copy T2 formula down, hide Column T, copy JF2 formula down.
 
Last edited:
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,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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