• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Array formula

W

will31

New Member
Hi All,


I have a two tables like the ones below;


Pricing Tool


A------ B------ C------ D------ E------ F------ G------ H------

-------Initial- 1------ 3------ 6------ 12----- 24----- 36-----

Test 1- 1

Test 2- 2----- TOTAL-- 380


--------1------ 2------ 3------ 4------ 5------ 6------ 7------ 8------ 9------

Test 1- 100---- 90----- 80----- 70----- 60----- 50----- 40----- 30-----

Test 2- 300---- 280---- 260---- 240---- 220---- 200---- 180---- 160----


I would like to be able to enter the number of occaisions a test is conducted and get a total on the bottom line. The price for the test is a sliding scale based on the number of occaisions the test is used. For example in table 2.


Is it possible to calculate this with an array formula rather than have multiple lookups / calculations for each test?
 
Do you need to multiply the number of tests by the cost looked up, or is the cost looked up the total price? In your example, the case seems to be the latter, but from a financial side, your data would say that the more tests I conduct, the lower the overall cost.


Please clarify?
 
Hi Luke,


The table contains a price per sample value, this decreases as the sample number increases due to economies of scale.


The price for each row therefore is the total of the cost per sample by the number samples for each of the tests.


e.g.


Test 1 1 sample = 1 x 100

Test 2 2 samples = 2 x 280


total is 660


god my maths is bad, the original total is wrong!


Thanks for replying,


Will
 
Why wouldn't you just use 2 HLOOKUP formula

eg:

=B6*HLOOKUP(B6,$B$10:$I$12,2,0)+=+B7*HLOOKUP(B7,$B$10:$I$12,3,0)


Assumes your

Test 1 No is B6

Test 2 No is B7

Lookup table is B10:I12

adjust accordingly
 
Hi Hui,


I have 10 tests, I thought of using lookups but the formula would be pretty big. Is there another way?
 
You can use Data Tables to achieve this type of tabulation

Have a read of http://chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/
 
You must log in or register to reply here.
Back
Top