Calculate a Measure using a constant from an unrelated table

SteviebLGX

New Member
Joined
Dec 7, 2019
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I have written a measure that calculates the volume of a number of items for an order. I now want to write a measure that would calculate how many cartons would be needed for that order. There are three carton sizes which I have defined in a separate, but unrelated table. In essence the calculation would be

NumberOfSmallCartons:=TotalVolume/SmallCartonVolume
NumberOfMediumCartons:=TotalVolume/MediumCartonVolume
NumberOfLargeCartons:=TotalVolume/LargeCartonVolume

The Carton Volume Table is something like this;

Small,900000
Medium,125000
Large,200000

I need to be able to reference the Carton Volume Table by creating a 'temporary' relationship by virtue of 'Small', 'Medium', or 'Large'.

Or do it a better way.

Any advice/suggestions would be appreciated. Thanks

(I have asked this on another forum, but with no response. Apologies if this is a repeated question for some)
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Welcome to the Board. Please provide the link to the cross-post so that potential helpers can review and determine whether to invest time responding.

Is your Small volume 90000 rather than 900000? What are the constraints on your problem? Are you seeking to have the least quantity of "over" volume, minimize the total number of containers, etc?
 
Upvote 0
Welcome to the Board. Please provide the link to the cross-post so that potential helpers can review and determine whether to invest time responding.

Is your Small volume 90000 rather than 900000? What are the constraints on your problem? Are you seeking to have the least quantity of "over" volume, minimize the total number of containers, etc?
Yes, you are correct - it should be 90,000.

The purpose of the calculation is to determine how many containers of each type would be needed. It is a stepping stone calculation really.

What I want to know is if I have a table of 'constants' like this how can I reference them in Measures.

(I will try and find the cross-reference).
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

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