Take data from tables on other sheets

[iviv]

This is a little complex, so unfortunately has gone past my excel skills. As a bit of background info, I'm working on a quick spreadsheet to calculate the cost to ship a parcel to different countries. I've got a worksheet with the list of countries, which services are offered to that country, and what zone for pricing each service runs in.
Using data validation I've got a dropdown menu to pick the destination country, and used vlookup to return the price zones for each service, returning a 0 if that particular service is not available.

Now, I've made a separate worksheet containing our pricing data for each service, so a sheet containing all the express prices, a sheet containing all the standard prices, and so on. This has all been done by hand before with a book, look in the book for the country you want to send to, see what service and what zone. For instance, if I wanted to send something to Germany, that would be zone 3 on the express service, and zone 4 on a standard service. I would then go to the express table, look at the zone 3 column, go down to the weight of the parcel (rounded up) and that would tell me how much it will cost.

To make it a little easier, here's a screenshot with all the values removed.

http://i.imgur.com/mTsp5.jpg

On my front worksheet I have the weight of the parcel, and what zone it is to sent it on the express service. So I somehow need a way to make excel look at the weight, and compare it to the weights in this table, rounding up to the nearest value on the table (Since the increments increase as you get heavies, it starts off increasing by 0.5Kg for the first 10Kg, then increases to 1Kg, and then 2, and so on), and then return the cost from the column of the relevant zone.
For example, if I had a parcel that weighted 20.3Kg going to zone 3, I would want it to look down the weight column, and because 20.3Kg is higher than 20, it needs to use the 22Kg row, and then it goes across to the zone 3 column and returns the value in that cell.

And then I would then copy this formula for each cell, so on my front page it would return the cost from the express sheet using the above, then on the standard sheet it would do the same, just looking at a different sheet and a different zone.
http://i.imgur.com/xgyt5.jpg
There's a copy of the front page. As you can see, I've used Vlookup to return the relevant zone for each service. I now need to use the 'greatest weight' column along with the service level (express plus through to expedited) and use the table I posted before to look down and get the price for that weight going on that service to that zone.

I hope this has all made sense. Its making sense in my head, but that doesn't really mean much, does it?

Anyway, is there an easy way to do this? I assume that the table columns would need to be changed to remove the 'zone' from each column, which is hardly an issue, it should be possible to use the same formula, and just change the worksheet it gets the data from.

Thanks in advance!

[dilipandey]

Hi iviv,

Suggest you to upload the sample workbook not images. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[iviv]

Thanks for the reply. I've changed all the values around in the tables, but otherwise left it the same. I'll try and run through an example as you open it to try and help make more sense of it.

For instance, the parcel I have in there at the moment has a weight of 26.3Kg and I want to send it to Germany. This is zone 3 for the three express options, but zone 4 for a standard delivery, and there is no expedited delivery option.

To send it express plus, I check the express plus table, round it up to 28Kg, and go across to zone 3, and it would cost me 34.00
For express, I would do the same and it would cost 34.00 as well (These values would be different in the actual table)
For express saver, it would also be 34.00
For standard, it would round up to 27Kg and not 28Kg, and its zone 4, so would cost 28.5

On the front sheet, it lists all the delivery speeds. Under each one I would like it to reference the relevant table, using the weight provided from the front sheet, and the zone also provided on the first sheet to retrieve the cost. The first problem I have is with rounding the weight up. Its not a simple round it up to the nearest whole number as lower weights get rounded up to the nearest 0.5Kg, and heavier weights get rounded up to the nearest 5Kg, but also this depends on the service. For instance the standard one in the example above went up to the nearest 1Kg, while the express ones were rounded up to the nearest 2Kg.

The other problem is actually referencing the table. I imagine I could use a Vlookup. Something like:
=VLOOKUP(I4 <Inset some way of rounding this>, ExpressTable, (D7+1), 1)

Which would, I think, find the weight in the express table, and return the value from column (Zone+1) because the weight column would be the first in the table, so Weight would be the first column and Zone 1 would be the second column, hence the (zone+1). The only problem is with the express columns having Zone 4, 41, 42, 5 and 51 after each other, as that would prevent the Vloookup from working properly. I could possibly rename the columns and not use the same zone names given by our carrier if needed, though.

Anyway, I hope this has made things clearer!

IRtest2.xlsx

[dilipandey]

yes.. this is more clearer.

I believe this need to be solved in phase by phase.
Let's first pickup up ROUNDING issue :-

you have to round a weight to:-

1) nearest 0.5 kg or,
2) nearest 1 kg or.
... list down other roundings as well ?

and then mention what will be the trigger to do this rounding ?
i.e., if this depends on the service, then what is the "relation table" for this ?

suggest you to post a sample to cover just the above point only and after solving this one, you should proceed further. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[iviv]

Hi dilipandey,

For the rounding, the figure it needs to be rounded to unfortunately depends on the nearest relevant number in the various tables. For instance, if you go to the express saver worksheet, there are set prices for each weight. The weight bands are set by our shipper, so we can't change them. For instance, in the example I gave above with a parcel weighing 26.3Kg, on the express saver table it would have to be rounded up to 28Kg, since it is over 26Kg. So the price would be read from along the 28Kg row.
However, on the standard service, it would only be rounded up to 27Kg, because there is a 27Kg price row.

Equally, sending a letter which weighs 0.3Kg would be rounded up to 0.5Kg on the express saver table, and to 1Kg on the standard table.

On the first worksheet, in the cell C9, I would like it to show the price for sending the weight of the parcel in I4 to the zone in C7 in the worksheet 'Express Plus'.
The Cell D9 would do the same, but use Zone D7 in the 'Express' worksheet.


I've been thinking about the problem overnight, and come up with a semi-working solution.

IRtest2.xlsx

I've altered the zones in each worksheet table to make it easy to manage using Vlookup, and altered the zones in sheet 2 accordingly. I've also removed the 'over xKg' option from the bottom of each worksheet as that's overcomplicated and not something which I'd need to use in this sheet. I have also altered the numbers in each sheet since previously for most expressx options it was giving the same value as they are usually the same zone.

Anyway, I have used the formula =VLOOKUP(I4, ExpressPlus, (C7+1), 1)
It's almost working, but the problem is that Vlookup is rounding down to the nearest weight, rather than rounding up. I tried flipping the table, so that the largest weight was at the top, but then it gave me a completely wrong answer, rather than being one row away from the right answer.
In the spreadsheet in this post, it has the Plus cost at 34, express at 35, saver at 36, standard at 27.5 and there is no expedited service.
However, these should actually be Plus cost at 35, express at 36, saver at 37 and standard at 28.5

Altering the weight entries does change the costs, but again they always return a value from the table rounded down rather than up. The only time it is correct is if the weight exactly corresponds to a weight listed in the table.

Hopefully this clears things up a little!

[dilipandey]

Hi iviv,

Thanks for the detailed description.

While I was reading it, I was looking for triggers to do the rounding and I got finally my answer at the lines:-

In the spreadsheet in this post, it has the Plus cost at 34, express at 35, saver at 36, standard at 27.5 and there is no expedited service.
However, these should actually be Plus cost at 35, express at 36, saver at 37 and standard at 28.5

I would suggest you to look into MRound function using which I have tried to obtain the desired result but not sure, see the yellow cells in the attachment. Would suggest you to look into your query from a programming set of mind and then come up with some defined dynamic criteria using which we can instruct a machine to provide results.

Regards,
DILIPandey

<click on below 'star' if this helps>

[iviv]

Hi dilipandey,

After thinking over it tonight, I've decided to approach it from a completely different direction. As there are a set number of price bands, I've decided to use nested IF statements instead. Not the tidiest way to handle things, but its providing the correct results so far.
The only question I have is with the ROUNDUP command now.

So far, for the Express Plus option, I have the following command:
=IF(I4<10,(ROUNDUP(I4*2,0)/2),IF(I4<20,ROUNDUP(I4,0),0))

So for the less than 10Kg parcels it rounds the number up to the nearest 0.5 and stops. Otherwise if its less than 20 it rounds it up to the nearest whole number. The ,0 at the end is just to make sure the command works while I try to figure out the rest of the nested IF statement. I now need it to Round it up to the nearest 2 if I4 is less than 30, and to round it up to the nearest 5.
How do I get the roundup command to round to the nearest 2, and to the nearest 5?

Thanks!

[dilipandey]

Hi iviv,

How do I get the roundup command to round to the nearest 2, and to the nearest 5?

To achieve above, you can include following:-

=IF(I4<30,MROUND(I4,2),MROUND(I4,5))

Regards,
DILIPandey

<click on below 'star' if this helps>

[iviv]

Thanks again for the reply!

I tried that, but unfortunately the MROUND doesn't purely round up, it will round down as well. For instance, I would need 26.1Kg to round up to 28Kg, rather than down to 26Kg.

Thanks!

[Søren Larsen]

Try using =CEILING()

[dilipandey]

Hi iviv,

Anything after 24.49 would be rounded up to 28 using below formula:-

=MROUND(A1,7)

Regards,
DILIPandey

<click on below 'star' if this helps>

[iviv]

Try using =CEILING()

That's got it working perfectly! Thanks!
And thanks for your help as well, dilipandey, pretty much got it all working perfectly now. Just need one more thing figured out, but I think I'll make a second thread for that as its a completely different question to what was asked in the first post now!