combine simple formulas into if/then

[amyfb]

I'm trying to update a single condition if statement to a two-condition statement, and need some help!

I can successfully execute an isblank() and an isnumber(find()) formula, each in separate columns.
but when I try to combine them into an if(and or and ifs(and, with xlookups,
I cannot get correct results.
i tried replace named ranges with direct cell references, but that didnt' make any difference.

I am fully puzzled why they won't work.

all ideas welcome, thanks for looking.

 

[amyfb]

I am having some success with replacing FIND with SEARCH. Looks like I might be okay with that.

 

[amyfb]

I am not having any success nesting an if then with four conditions. Is the file that I provided not helpful? I didn't think this was an advanced issue, and am surprised no one has had any ideas for me to try.

 

[Roger Haney]

To me, your first sentence in your first post is not very clear. Is "AdjustTheFactor" a named range? If so, where is that range? In your posted file where are any of the ranges referenced in your formula located?

 

[amyfb]

To me, your first sentence in your first post is not very clear. Is "AdjustTheFactor" a named range? If so, where is that range? In your posted file where are any of the ranges referenced in your formula located?

Roger thanks for looking. I am using named ranges and have been able to successfully access them in other formulas and functions in the same file. So I was focusing on the structure of the if then construct that seems to be eluding me.

I've taken the recommended build-it-from-the core-out, and still run into bumps when trying to combine the pieces.
Doesn't matter if I try to insert pieces into existing formula that works, or if i try to build it up from the pieces that work independently.

The file here has original formulas that work, along with all the 'test the new pieces' columns, So, now, i need to add a set of conditions and calculate the same thing with data from different sources.
I'm stumped on why I cannot stitch it all together.

See if this uupdated file helps you see my issues. I've been attacking it from an IF{AND point of view, and now recent youtube video watching has me wondering if I'm in an IF(OR situation. so I'm headed there after a short break. Meanwhile, any light you might be able to shed on the issue is welcome.

thanks again for looking.

 

[AliGW]

The formula refers to files on your su=ystem:

=IF(ISBLANK(AdjustTheFactor),AdjustTheFactor*XLOOKUP(NONSTOCKSKU_Source,'https://1861234permabond-my.sharepo...t_Costing.xlsm'!FrgtDuty_Table[Duty])*tfc_Bom,"next")

 

[amyfb]

The formula refers to files on your su=ystem:

=IF(ISBLANK(AdjustTheFactor),AdjustTheFactor*XLOOKUP(NONSTOCKSKU_Source,'https://1861234permabond-my.sharepoint.com/personal/tyler_lamunyon_permabond_com/Documents/Desktop/072322-Demo_AdHoc_Product_Costing.xlsm'!FrgtDuty_Table[Source_Name],'https://1861234permabond-my.sharepoint.com/personal/tyler_lamunyon_permabond_com/Documents/Desktop/072322-Demo_AdHoc_Product_Costing.xlsm'!FrgtDuty_Table[Duty])*tfc_Bom,"next")

I don't know how to make the application generic without putting in hours of work to rebuild it with dummy data.

All I can say is that I need help understanding why individual pieces of the formula will work but cannot be stitched together for correct results.

my original formula checks for one blank cell then does lookups to perform calculations. works fine.
my updated formula needs to check for one more conditoin (match a text value in a cell) with the blank cell and do the same kind of lookups and calculations.
all the lookups work fine on their own.

Isblank() works fine.
isnumber(search()) works fine
if(is number(search()) works fine
if(isblank()) works fine.

if(and(isblank(), isnumber(Search())) WILL NOT WORK

There must be something about the IF(AND construct that is not compatible with the results of the isblank or isnumber, but I can't figure it out and do not know where to look.

 

[amyfb]

cross posted - https://answers.microsoft.com/en-us...an-ifand/b405e420-a55b-4010-970e-2365a0af1413

 

[amyfb]

i hope this stripped down model works to illustrate the issue.



on sheet 1 is where my trials and errors can be seen.



the dashboard has the input values, (N5, and J10:J34) and the data tab has the lookup sources.



I'm sure the nested if is failing because the formula is incomplete in some way; I've been beating it all afternoon between other crises at the office.



conditional 1: the sku = nonstockitemsale AND adjust the factor is blank - use the factor_qty * the lookup values

conditional 2: the sku - nonstockitemsale AND the adjust the factor has a value - use the adjustTheFactor * the lookup values

conditional 3: the sku <> nonstockitemsale AND the adjust the factor is blank - use the factor_qty * the lookup values

conditional 4: the sku <> nonstockitemsale AND the adjut the factor has a value. - use the adjust the factor * the lookup values



Hope this helps you or anyone help me.

 

[AliGW]

I don't know how to make the application generic without putting in hours of work to rebuild it with dummy data.

Anyone helping will have to do this to be able to work out what's wrong. I don't believe it's your helper's job to have to do this in order to be able to help you - sorry. I'll see if your latest workbook is any easier to follow.

 

[p45cal]

I haven't looked in-depth at your long formulae and have little idea what you intend for them to return, so these are only suggestions/queries, and I may have missed the point:
Looking at Sheet1:
In the cell F6, you have:
=ISNUMBER(FIND(SKU,"NONSTOCKITEMSALE"))
and I query whether this should be:
=ISNUMBER(FIND("NONSTOCKITEMSALE",SKU))

In cell J6 you use the AND function and it's not working for you; perhaps try:

=IF(((ISBLANK(ADJUST_the_Factor)=TRUE)*(ISNUMBER(SEARCH("NONSTOCKITEMSALE",SKU))=TRUE)),2*3,"next")

(this could be shorter!)

The same applies to M6:

=IF((ISNUMBER(SEARCH("nonstockitemsale",SKU)))*ISBLANK(ADJUST_the_Factor),"two conditions","faile")

As I say, these are only 'first glance' observations…

 

[amyfb]

P45cal has an interesting approach to the formula that I'm going to try.

to clarify:
The objective is this:

to modify the existing formulas on the dashboard in cols L10: M34, such that they accomodate the new conditional associated with the 'nonstockitemsale' sku that has to get its values from a different table based on a selection in dashboard cell N5.

the existing formulas work perfectly. the 'nonstockitemsale' sku is a one-off wrinkle that needs to be handled with formula revision.

so, the sheet 1 is me trying each part of the new conditions before trying to stitch it all together in a way that works. I was trying every idea I had, (find, search, etc) to see if one method was better than another.

hope that clarifies what I'm trying to accomplish here.

 

[p45cal]

I notice you have this formula (spaced out to make clear the two calculations to be made depending on the IF being true or not:

=IF(ISBLANK(AdjustTheFactor),

XLOOKUP(sku_bom,Data!$J$3:$J$1019,Data!$L$3:$L$1019)*XLOOKUP(sku_bom,Data!$J$3:$J$1019,Data!$S$3:$S$1019)
*factor_bom,

AdjustTheFactor *
XLOOKUP(sku_bom,Data!$J$3:$J$1019,Data!$L$3:$L$1019)*XLOOKUP(sku_bom,Data!$J$3:$J$1019,Data!$S$3:$S$1019))

This could be juggled around to give:

=IF(ISBLANK(AdjustTheFactor),

XLOOKUP(sku_bom,Data!$J$3:$J$1019,Data!$L$3:$L$1019)*XLOOKUP(sku_bom,Data!$J$3:$J$1019,Data!$S$3:$S$1019)*factor_bom,

XLOOKUP(sku_bom,Data!$J$3:$J$1019,Data!$L$3:$L$1019)*XLOOKUP(sku_bom,Data!$J$3:$J$1019,Data!$S$3:$S$1019)*AdjustTheFactor
)

from which it's obvious there's a great long chunk of similar calculation, so why not instead simplify to:

=XLOOKUP(sku_bom,Data!$J$3:$J$1019,Data!$L$3:$L$1019)*XLOOKUP(sku_bom,Data!$J$3:$J$1019,Data!$S$3:$S$1019)*IF(ISBLANK(AdjustTheFactor),factor_bom,AdjustTheFactor)

Looking closer you've got tables in place so why not refer to their columns using their structured references? Easier for a human to read.
Also I tried to do some multiplication within the XLOOKUP so check if this formula gives the right results (it helps testing if you have different Duty Factors rather than they all being 1 so you can compare your older formula results with new formulas):

=XLOOKUP(sku_bom,SKUs[SKU],SKUs[Purchase Price Ea]*SKUs[DutyFactor])*IF(ISBLANK(AdjustTheFactor),factor_bom,AdjustTheFactor)

If this works it's going to make it a lot easier to adjust the formula for non stock items (which I should get round to looking at later).