A woodworker in need of help with IF function with SUM as a condition

i may be missing something here
EDIT - not sure i'm even on the right sheet now - as Q2 - is not K9 has a 2 in NOT

As an example in field K9 you can see that the length of my table top is 1.4metres long.

i'm on sheet Test Template
But Q1 only at the moment

We are looking up the price of the wood - based on L2 - type of wood , and thickness K22

We have in your example
grapia 0.076

if i look in wood prices I see a table - with wood down column A and across row 1 - some numbers
looking at grapia - , I only see 2150 - under the number 52 in column G

in the example in P22 - you have 1782

populate P22 with the corresponding price from the "Wood Prices" worksheet.

so i'm not following your example
or which number on wood prices in row 1 to lookup

we can do a Grid lookup - which will look across those row1 numbers - find a match for - wood thickness and then go down that column untill we find the wood type and return the value in that cell that crosses both

OR we can use the lower of the values in the row to lookup thickness

not read q2 yet - will do next

Edit

looking at the IF statement
IF(K9>(J22/2),"SUM(M9/0.16)/2","SUM(M9/0.16))
and
IF(K9>(J22*0.18),"K2",K2/2 in sheet

"" changes to text and so will not calculate

IF(K9>(J22*0.18),"K2",K2/2 in sheet

IF(K9>(J22*0.18),K2,K2/2) in sheet
If K9 is Greater than j22*0.18 , it will do whats TRUE and so return the value in K2
Having "K2" - will return the TEXT K2

IF(K9>(J22/2),"SUM(M9/0.16)/2","SUM(M9/0.16))

Same here
If K9 is greater than J22 divided by 2
you will get the TEXT

,"SUM(M9/0.16)/2"
if not
then error
"SUM(M9/0.16

IF(K9>(J22/2),SUM(M9/0.16)/2,SUM(M9/0.16))
not sure why SUM
IF(K9>(J22/2),M9/0.16/2,M9/0.16)

i added an EDIT for the IF you have
may help

while i read you next post - after dinner

Edit

looking at the IF statement
IF(K9>(J22/2),"SUM(M9/0.16)/2","SUM(M9/0.16))
and
IF(K9>(J22*0.18),"K2",K2/2 in sheet

"" changes to text and so will not calculate

IF(K9>(J22*0.18),"K2",K2/2 in sheet

IF(K9>(J22*0.18),K2,K2/2) in sheet
If K9 is Greater than j22*0.18 , it will do whats TRUE and so return the value in K2
Having "K2" - will return the TEXT K2

IF(K9>(J22/2),"SUM(M9/0.16)/2","SUM(M9/0.16))
Same here
If K9 is greater than J22 divided by 2
you will get the TEXT

,"SUM(M9/0.16)/2"
if not
then error
"SUM(M9/0.16

IF(K9>(J22/2),SUM(M9/0.16)/2,SUM(M9/0.16))
not sure why SUM
IF(K9>(J22/2),M9/0.16/2,M9/0.16)

can you give some examples of what the results SHOULD be -

i think this will help better than I with a grid lookup
but this example uses exact matches - so the number in the Row 1 has to match with thickness in k22
It can use the nearest lowest value - or i suspect in your case you need highest thickness

http://skp.mvps.org/xl00002.htm

so if you can explain further with examples - the grid lookup should be changed to suite
But thats the basics

Also what version of excel are you using, profile needs to be updated with the version - as excel has lots of different function depending on versions - and solutions will vary

newest one will go out of date - newest would be subscription 365 version as that gets various updates and new functions quite frequently - hence why version number is also good to show - as in my profile

Here is a real world example based on the information that is currently populated in the template;

IN relation to:

1) I want my primary wood to be "Nogal USA" and I select that in K2 and I would like my seconary wood to be "Arce Duro" and I select that in K3.

As I move down to the section below that called Final Dimensions I want everything to be made of Nogal USA except the Stretcher so I select "2nd" in I13.

If I change the length of the table top in field L9 to 1.3 that means that when I come to cut my lumber which is 3.6 metres long (J22) I should be able to get two usable pieces from one length so I dont want to buy 6 pieces, I want to buy 3. This is the number I want to see in M22


Similary, in Row 12 cells K-N, I specify that I want 4 leg pieces that are 0.5m long (so 2m in total) I only need one piece of raw lumber for those elements so I would like M25 to calculate the total length of the legs and check that number is > J25 and tell me I need 1 piece.

In your EDIT for the If formula, when I paste that in it returns the value 6.25 (or if I round down, 6) but it should actually return 3, did I divide somewhere I should have multiplied??

Thanks for you efforts! I updated my excel version in my profile

EDIT I fixed my first If formula, I simply had the values in the wrong order. now I just need to figure our how to translate that equation to the other rows below that that have a different multiplier of pieces that fit into a single board......

i have tried to follow those notes - but getting a bit lost
can you setup the spreadsheet - and highlight the errors
when i got to

M22 has
=SUM(M9/0.16)
which is the width - so i dont see the connection to the length as mentioned

If I change the length of the table top in field L9 to 1.3 that means that when I come to cut my lumber which is 3.6 metres long (J22) I should be able to get two usable pieces from one length so I dont want to buy 6 pieces, I want to buy 3. This is the number I want to see in M22

So i dont see how changing L9 would change M22

which IF formula
IF(K9>(J22/2),"SUM(M9/0.16)/2","SUM(M9/0.16))
my correction
IF(K9>(J22/2),M9/0.16/2,M9/0.16)

FOR TREU
M9 is 0.8
0.8 / 0.16 = 5
5 / 2 = 2.5

FOR FALSE
0.8 / 0.16 = 5

really sorry a bit lost , as i dont know the whole process and so trying to sort out the formulas only

you need to get rid of all those "" on numbers - otherwise its TEXT
=IF(N9>0.07,"0.105",IF(N9>0.044,"0.076",IF(N9>0.032,"0.052",IF(N9>0.022,"0.038",IF(N9>0.011,"0.027")))))

=IF(N9>0.07,0.105,IF(N9>0.044,0.076,IF(N9>0.032,0.052,IF(N9>0.022,0.038,IF(N9>0.011,0.027)))))
Also needs a FALSE
=IF( Test, True , False )
so
nested
replacign the FASE - with =IF( Test, True , False )
=IF( Test, True , IF( Test, True , False ) )
Do that each time
SO
=IF( Test, True , IF( Test, True , IF( Test, True , IF( Test, True , False ) ) ) )

The reason I put the "" around the number is because I want the formula to return those exact numbers

As i say "" does not return numbers but TEXT
and so it maybe they are not then available for calculations.
So the way i posted will produce those exact numbers - so long as the cell is set to display the correct number of decimal places

Ok, so we are dealing with length and width - verses - RAW and finish sizes

So how many lengths can we get out
=FLOOR(J22/L9,1)

that should give the maximum number of pieces , in your example out of 3.6m length

so i have had a play - i am using the value

I have added a column so we have the finished size - included , so that cell can be used to calculate rather than work out with hardcoded in formulas - like /0.16 ... as if that changes as you mention, then ALL the formula would need changing

so now cell - M22 = the finished thickness

Now i have a small table - in range J31 to N35
Titles ETAF - Calculation

So first i want to find out how many lengths i can get out of the material

N32 - has this formula
=FLOOR(J22/L9,1)
which will work out the number of lengths we can get out of the material
so L9 is the length of table - and J22 is the material length - so we now know how many pieces of raw material will be needed , just to make the length of the table top

so if the table is 1 m and the raw material 3.6 - then we can get 3 x 1m lengths from that material
NOW how many lengths do we need considering we , we get 3 per length - to make up for the width
The width is in N9 now - as i added a column , so width = 1.4 , BUT we need the finished size - which is in M22 now 0.16
so again
EDIT - NOT QUITE WORKING as this should result in 9 NOT 8
Updated to use
Also updated and attached the file
But as seen it may have a mistake - sorry , await your review
=ROUNDUP(N9/M22,0)
will give the number
N9 the width divided by the finished size 0.16 - so we need 8 pieces of wood 0.16 width to make up the 1.4m overall width

Now we know we will get 3 pieces per length - so we dont need 8 length of 3.6 as we get 3 - now how many length of 3.6 will make up the 8 required at 3 per length
1 = 3 , 2 =6 , so to get 8 we must buy 3

so i have used in my table =ROUNDUP(N33/N32,0) in cell N35 - which gives us 3 lengths needed to make the length and width ......

Have a play with the length in J22 - raw material
length of table top in L9 and the width of the table top in N9

try a few combinations you know and see if the raw material quantity works - in my table N35

you could also change the finished width and see if that also adjusts correctly

i have highlighted the variable cells in your table in green
and then my table, does the calc to work out the number of raw material needed to make up the length and width of the table

anywhere near close to what you are after for that section

you are very welcome
But do check it out and make sure it gives you all the results for various possible inputs - you dont want to copy and setup somethign thats incorrect , that you then later rely on for costing

with regard the lookup - happy to help out , I have added a lookup to the range - see below - assuming an EXACT MATCH of the rows and column

I have added

=IFERROR(IF(INDEX('Wood Prices'!$C$2:$K$40,MATCH($K$2,'Wood Prices'!$A$2:$A$40,0),MATCH($K22*1000,'Wood Prices'!$C$1:$K$1,0))=0,"Material Not Available", INDEX('Wood Prices'!$C$2:$K$40,MATCH($K$2,'Wood Prices'!$A$2:$A$40,0),MATCH($K22*1000,'Wood Prices'!$C$1:$K$1,0))),"Not in Table")

to the range Q22 to Q29

Note this is using an exact match - as i understand those numbers now , and you seem to have covered
Note K22 is multiplied by 1000 to match the table

If the size or the wood type is not in the table you will get an error "Not in Table" - see Q29 , which i changed the size to 1
if there is no value in the table but both match - like Q26 , i changed the size to 0.015
then it will return a zero , which instead of 0 , i have added saying "Material NOT available"

you are welcome
Perhaps a reply for each question

A) MATERIAL NOT AVIALABLE

?? O13 - I used the cell K26?

not sure how that part of the spreadsheet works for the wood prices
can you explain further please

B)

Playing around with it and doing some tests I have discovered Im made a signicant oversight! When calculating the desired quanties of raw lumber the formula does not include cells K9-K16 which tells us how many of each element I have to build. (How many legs, or walls or table tops or whatever) Do you know of a simple way to incorporate this number?

Would that be simply multiplying the end result by the quantity - in K9-K16 ,
Not sure how my table example for Tabletops has been incorporated into your new spreadsheet -

I have also now thought, which i have no idea how to solve
lets say you have a table top - that works out you need 3 lenghts of 3.6m material - BUT it maybe that each length piece has maybe a 0.6m waste
so you have 3 lenghts of 0.6m at the same thickness as table top - (may apply more to other elements) - BUT lets say the legs are 0.5m and same thickness
well those 3 lengths will do at least 3 of the legs
as i say NOT sure how to work that bit out
And i suspect there maybe a lot of waste , looking at the individual items and calculating the required lengths - but not using the waste
As i say - no idea how to incorporate that and how complicate it would be

ok, was looking at the thickness question will come back to that

for the different wood - then its easy to do a different grid lookup for price

by adding in AN IF based on the dropdown 1st/2nd, which i have also added to the RAW table

=IFERROR(IF(INDEX('Wood Prices'!$C$2:$K$40,MATCH(IF(I22="1st",$K$2,$K$3),'Wood Prices'!$A$2:$A$40,0),MATCH($L22*1000,'Wood Prices'!$C$1:$K$1,0))=0,"Material Not Available", INDEX('Wood Prices'!$C$2:$K$40,MATCH(IF(I22="1st",$K$2,$K$3),'Wood Prices'!$A$2:$A$40,0),MATCH($L22*1000,'Wood Prices'!$C$1:$K$1,0))),"Not in Table")

What other things changed based on the material change ?

you also have a conditional format and dropdown for 3rd material type - is that also needed


looking at the thickness - would need to know if that NEXT size up is available in the wood price - looking into - maybe difficult

I have added a conditional format to column S range in RAW Material
Also in Column T added an IFERROR - so that the total can still be summed

i wont work any more - on previous posts - and wait for you to come back with further questions
As you are also happy with some of the assumptions, I may be working on something that is not necessary for your "real Life" situation

Originally Posted by etaf

What other things changed based on the material change ?

you also have a conditional format and dropdown for 3rd material type - is that also needed

I found an error in how I had calculated the total number of boards in P22-30. Ive fixed it now but adding anothe couple of columns in the section N35:R44

It looks pretty good at the moment for calculating material quantities.

To answer your questions, to have a 3rd material option is a nice to have but not a must have. We use 3 woods in no more than 10% of our projects so I can live without it if its a pain.

So to answer your question about what changes based on material, well, everything! The work we have down so far is just the foundation of our quote but then all these numbers get translated to the left hand side of the worksheet where I use them to:

1. calculate D3, total wood costs,
2. B12 where I am able to calculate how many linear metres of wood I have to process and therefore the cost of that
3. B15 (the number displayed in this cell is a number of hours) which is what I need to work on next. this is how many boards do I have to glue up. For every shelf I glue up two boards that they are 1m long. That takes me 15 minutes. but it the shelf was wider and incorporated 3 boards it would add another 5 minutes to the time and an additional 5 miutes for every additional board. If it's longer than 1 metre it also adds another 5 minutes to the process to a 2m long shelf that was 40cm long might take me 15 minutes more to glue up. Then I have to calculate all the total number of glue ups in the piece. If as in the worksheet below there are 7 seperate glues up (Total of K9-K16) then I have to work out how long they will all take. So this is my next challange!
4. B16 and B17 use the total sqaured meteres of surface aread from O17 and double it (to represent the two sides of each piece) Which tells me how many square metres of sanding and finishing I need to do on the piece. Idealy I should have all of the Row B displaying a number of hours so I need to calculate how long it take me to both sand and apply finish to a square metre of finsihed product.
5. Then finally B22 should total the production hours and I can then calculate my overheads.


Does any of that make any sense?

New Template 2022 V4.01.xlsx

B12 = U26 - is that correct , should it be U30

you dont need the sum
just =U30
like you have in D3

No3. looks like a big thing to work out

4. - same formula , if thats what you need to see

5. Not sure what G12 is

adding a 3rd material on the lookup is easy to do , if its just the grid lookup

=IFERROR(IF(INDEX('Wood Prices'!$C$2:$K$40,MATCH(IF(I22="1st",$K$2,IF(I22="2nd",$K$3,$K$4)),'Wood Prices'!$A$2:$A$40,0),MATCH($L22*1000,'Wood Prices'!$C$1:$K$1,0))=0,"Material Not Available", INDEX('Wood Prices'!$C$2:$K$40,MATCH(IF(I22="1st",$K$2,IF(I22="2nd",$K$3,$K$4)),'Wood Prices'!$A$2:$A$40,0),MATCH($L22*1000,'Wood Prices'!$C$1:$K$1,0))),"Not in Table")


Glad its working so far

Is it the time you are thinking of working on next ?

In the example above I am preparing an estimate to make 30 small plinths for some trophies. They are only 21cm long and 7cm wide with a thickness of 2.5cm. In this case not onky would I be able to get multiple pieces out of own length but I would also be able to get two pieces out of the width of our board too (which if you recall is 0.16 once is has been prepared) Any guidance about where to put that formula?

Ok, so the width of the raw material is finished at 0.16 of the board
and we use figure to then work out how many we need to make up the required customer width - which in the past had been multiple boards -
Now because the width is ONLY 0.07 - then we can get 2 out of the board finished width of 0.16

the raw is 18 and you reduce that to 16 for the finished size -
to cut that that material in half - depending on the Kerf size
18 / 2 = 9
then allowing the 2mm for finishing - the smallest you could make is 0.07
so in this case you can get 2 bits OUT
would
Would you go smaller and get 3 or 4 out of a 18 raw material
then we can test to see if the width is a multiple of the RAW size and have to also allow for finish

OR is 0.7 or smaller always - just going to be 2 bits
if its 0.9 , then cannot be 2 from 1 piece

The next bug I run into is that you will see that all fields from K10-16 are empty but for some reason O23-O29 are populated which in turns populates all the calles in columns R,T and U which then throws out all of my calculations. How do I get the sheet to return 0 in Cells O22-29 if that is the value in K9-16?

k22 to K29 - how is that filled in ?
OR is that just your standard raw material table for those type of items , and remains constant all the time

The RAW lumber table - for J20 to R29 - is just preset based on the Lumber RAW material entries

IN S22 have added an IF - which looks at the customer Quanitiy - and if its Zero 0 , then it blanks out that part
so
=IF(K9=0,"",IFERROR(IF(INDEX('Wood Prices'!$C$2:$K$40,MATCH(IF(I22="1st",$K$2,IF(I22="2nd",$K$3,$K$4)),'Wood Prices'!$A$2:$A$40,0),MATCH($L22*1000,'Wood Prices'!$C$1:$K$1,0))=0,"Material Not Available", INDEX('Wood Prices'!$C$2:$K$40,MATCH(IF(I22="1st",$K$2,IF(I22="2nd",$K$3,$K$4)),'Wood Prices'!$A$2:$A$40,0),MATCH($L22*1000,'Wood Prices'!$C$1:$K$1,0))),"Not in Table"))

Which will also now work for the Price in T - as there is no entry - so thats just blank

But the U the meters also needs to look at the quantity
so i added U22
=IF(K9=0,"",SUM(K22*O22))
copied down

So now the price is not calculated or the material for any ITEM thats has ZERO in the customer requirement

We could blank out the entire table - BUT i dont know if you want to keep the raw material info - so you dont have to keep retyping and its just the PRICE and MATERIAL Length you NEED
S, T & U

I could do something like conditional format the TEXT FONT in J22 to U29 to be White - if a Zero in K9 to K16 is a ZERO
often done in spreadsheet to trick the user to think its blank - when in fact all the text is there , if its a standard table
- just for fun i added this to the spreadsheet attached called
"New Template 2022 V4.02 - Bug 2 ETAF - With conditional Frmt"

but the BUG 2 is fixed as described above in with

New Template 2022 V4.02 - Bug 2 ETAF

All i did with the "New Template 2022 V4.02 - Bug 2 ETAF - With conditional Frmt" was add the Conditional format condition for J22 to U29 - selected that range
the used a formula
$K9=0
so now if a zero in K9 - all the font in J22 row will be changed to white
same for for the other rows - it just looks at K9,10,11,12 etc - one ruie - Fixes the column using $K so each column test the same column and moves down 9 = 22 , 10 =23 rows etc

I thought is easier to show , having re-read my description and very simple to do

Anyway - await your responses from previous post now

Glad to see you are using a version control to keep track of your spreadsheets - as i'm NOT - just using your latest attached version and updating - with ETAF at the end

can we use some sort of factor range

so between X and X = 16/2 - 2x the board size , between Y and Y - then 3 x

so in A37

=IF( M9 < 0.16 , m9 > 0.06 ) , then 2x , IF ( M9 < 0.16 , m9 > 0.04 ) , then 3x , ELSE =ROUNDUP(M9/N22,0)
sort of thing

BUT I need the exact range of values
for when its 2 bits
for when its 3 bits

And if its > 0.16 then we use the formula we already have
sort of thing

Thinking about it - we could say

=If( M9 < = 0.05 , 3 , IF( M9 < 0.75 , 2 , 1) ) * formula to work out number

so whats the maximum number for 3 , then 2

If I change one of those dimensions in the Raw Lumber section, (for example if I have a particular piece of wood I want to use and I want to enter its real dimensions rather that using the generic dimensions), will it damage the formula? Can I enter those specifc dimensions at any time? (Or do I need to enter the raw dimensions before/after I enter the values in the Final Dimensions?

No issue - all it does is change the font colour from default - Black to White , so it blends in with background

When you click on the cell - you will see the text in the formula bar - with the X and tick next to it at the top of the sheet

change away

Awesome. In your opinion is this now becoming unstable? In the sense that the formulas are so contingent on various cells that is becomes more fragile as a usable worksheet? I really have no idea where the boundries are between usable and overly complex

yes, as it can become an issue when you need to change things , and you have things dependant on other things
which is why I like to copy things down into cells - so the tables are all referenced
to that end you could copy the cell with quanity to the front of the RAW table like - i had with the 1st , 2nd - then when looking at the RAW table you will immediately see a zero for quanity rather than refereing a different table
BUT then thats more duplication - which i tend to be happy with, if it makes things easier to see and maintain

we careful planning , it should be fine - no major calculations to slow things out

Just need to understand the interactions between the customer requirements table and the RAW table

worked on multiple sheet more complex spreadsheets before

i remember on for a call centre and call perfomance - manpower , when working on a change management - improvement program across 3 centres and goal was saving costs ...
so no worries at the moment, and as its developed things will go a drift - but thats the development cycle

EDIT _ changed my mind - see below

< = 0.025 = 5x
< = 0.035 = 4x
< = 0.05 = 3x
< = 0.07 = 2x
else 1

we can use a lookup for that , as a multiplier - in fact just did that on another forum today

I have just as an example put a load of possible width - and then produced the X factor
In column W and X have a look and see if that works

not going to stay in the sheet
BUT it just as a test for the various values and make sure we get , 5x 4x 3x 2x 1x

Now in the example you put 0.07 in the cell

BUT do you keep, this in the same measurement
so a table top of 1.4 width - in a previous spreadsheet - is that the same metric - ie all will be in CM or do you change from Cm to Metres etc ......

just need those measurement to always be the same

i'm assuming we would add that factor into the cell in O37 BOARDS/WIDTH