Associating items with numbers to calculate postal cost according to weight

Don't worry there's quite a bit to it.

A few different ways you could do it. Your first research topic is named ranges. You could assign a named range to any associated values. Or you could store values in a table; your second research topic is structured references (like named ranges but referring to table references).

Your third research topic - nested if statements https://exceljet.net/formula/nested-if-function-example. This guy writes great articles, but here nested statements can also be nested in the if false parameter. Giving much more flexibility (and easier coding) as they can be tricky to get your head around them.

So don't beat yourself up. What you're trying to do is a a little tricky. I've been working on a very complex spreadsheet with conditional logic (if statements and the like) for around 6 months now. It's not really a spreadsheet, it's more like software and it wouldn't take much to hard-code them into software really; python or whatever. But it's tricky to get your head around the logic gates & ensure you have all parameters covered in non-linear application (logic gates from different angles). What can help is to sketch the idea out on a flow chart, I couldn't have done 3/4 of my logical gates without this.

Carl - as requested in post #2, please attach a workbook and one of us will give you some suggestions based on your data.

Unfortunately the attachment icon doesn't work at the moment (it hasn't worked for years, and despite our repeatedly asking the technical team who own the forum to fix it, they can't be bothered to do so), so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

What do you mean? As you can see if you glance at other threads, many people attach workbooks successfully. In what way are you stuck?

I don't quite understand, I'm afraid. Can you add some expected outcomes to your workbook, manually calculated, and explain the logic of these outcomes? Where are the results supposed to appear?

This all seems to be in an odd order, but here's the first bit for a starter in I2:

=IF(A2="","",VLOOKUP(B2,$Q$17:$R$33,2,0)*A2&"g")

Can you confirm which columns will be entered manually and which should be calculated in the main table.

Thanks for the formular.
Lol sorry thats the way my head works and its a bit upside down.
I don't mind if you change the order.

In the Main Table Columns A, B, C and M (Item Cost) are entered manually.


Edited
Regarding the I2 Formular I tried to work it out and Wow i tottaly get it.
I can use that Formular a lot.

OK - thanks. I'll have another look.

How is Excel meant to determine the weight of the package?

Excel 2016 (Windows) 32 bit

	D	E	F
1	TOTAL	15% FEE	SUM
2	=C2*A2	=D2*0.15	=D2-E2

Sheet: Sheet1

Can you share you latest workbook?

We are here to help!

This makes things a bit clearer, thanks. I may not be able to look at this until this evening, but someone else may step in before then.

=IF(A2="","",VLOOKUP(B2,$Q$17:$R$33,2,0)*A2&"g")
I only got stuck on one thing though. At the bottom of the column I when i add up totals.
for example when i do =I2+I3+I4 or use the =SUM(J2:J4) etc. to include the "g" in the total.

If you are not aware of it when you concatenate it returns a text variable type. Its numeric value will always be 0. Math operations applied to those will always return an error.

You can however apply a formatting "mask" to the numbers and thereby retain the desired numeric values.

1. Select I2:I19.
2. On the ribbon click Home. Then in the 'Number' group click the diagonal arrow in the lower right corner.
3. Select 'Custom' from the Category.
4. In the 'Type' box type in General "g"
5. Click OK.

The numbers will display in-cell with the "g" appended, but in the formula bar you will see just the formula (or just a number).

Hi Dave, Thanks for replying to my quest :-)
I have tried what you have suggested from I2:I19 and I2 and I19 but it apears locked and i cant change anything in there.

When i do this with I2:I19 it shows 54g in the sample box but it cant be edited.

Try modifying the formula in cell I2 so that it reads: =IF(A2="","",VLOOKUP(B2,$Q$17:$R$33,2,0)*A2)
Copy the formula down to cell I18
Now follow the steps listed in Dave's post. (Result should be that cell I19 displays 1452g)
Let us know if you have any questions.

Here is the file with the formula and format changes applied.
Let us know if you have any questions.

Thank you, I will download it and compare where i am going wrong.

Edited...

I was making the mistake by not adding 0"g"
I was just adding g to the Numbers / Custom options and nothing else.
Thanks again.

Please could you help me with the postage formulat on J2:J18
Basically i believe it is a if less than formular ?
I think that i can figure out the rest with the previous help.

Two things are needed.
1. Change the text values in AA28:AA33 to numbers (custom format 0"g")
2. Populate J2:J18 using: =INDEX(AC$28:AC$33,MATCH(K2,AA$28:AA$33))
Let us know if you have any questions.

This proposal employs two helper columns (P:Q) which may be moved and/or hidden for aesthetic purposes.
Column P displays the row number using: =MATCH(IFERROR(LEFT(B2,SEARCH(" ",B2)-1),B2),U$28:U$33,0)
Column Q displays the column number using: =AGGREGATE(15,6,(COLUMN(W$28:Z$28)-COLUMN(V$28))/(INDEX(W$28:Z$33,P2,0)>=A2),1)
The envelope size column is populated using: =INDEX(W$26:Z$26,Q2)
The envelope weight column is populated using: =INDEX(W$27:Z$27,Q2)
Let us know if you have any questions.

The .xlsx file is attached to post #30.
I did neglect to mention that I changed the envelope weights (row 27) from text to numbers custom formatted 0"g"
Let us know if you have any questions.

It seems to me that if 7 Came C's are to be shipped only 6 will fit in an F3 envelope and 1 will need to be shipped in an A000 envelope unless there are larger sizes of envelopes not included in the envelope size table.
Assuming the former to be the case the #NUM error (i.e. number of items is too large) seems appropriate and two lines will be needed to compute the postage on this order.
Notice that in the attached copy of the file that C6 now shows 6 and C7 displays 1.
(Row 7 was produced by selecting A7:V7, Pressing the Ctrl, Shift and = keys and selecting OK. Once a new row is inserted press the Ctrl and d keys then change C6 to 6 and C7 to 1)
Similar treatments of rows 12 and 16 should clear all of the #NUM errors including those in column H.
Let us know if you have any questions.

From the experience here i have also answered a different question:
To calculate other sheets in the Workbook to add up totals. I also managed to calculate formulars from different workbooks

How would i change the Postage value to £4.55 in column H as soon as it goes beyond number of units to fit F3 size ?
Try pasting the following into cell H3 and copy down: =IFERROR(INDEX(AA$15:AA$20,MATCH(Q3,Y$15:Y$20)),4.55)
Let us know if you have any questions.

Sorry, Carl - you may not ask this here. You must start a new thread for this as it is completely different and requires its own suitable thread title.

This thread should be marked as solved, please. Select Thread Tools from the menu link above and mark this thread as SOLVED.

Note to all participants: no answers to this new query here, please.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.