+ Reply to Thread
Results 1 to 9 of 9

Multi -array / -table Price Calculation Formula

  1. #1
    Registered User
    Join Date
    04-20-2005
    Posts
    48

    Multi -array / -table Price Calculation Formula

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    I need a formula that calculates the following:
    1. Look up value of letter from the second table (e.g. x=100, y=200, z=300)
    2. For a given row, multiply the values (e.g. 100*1*5, 200*3*15, 300*2*10)
    3. SUM the values from step 2

    ...all in one cell for one total calculation.

    Thoughts?

    Thanks,
    JC

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: Multi -array / -table Price Calculation Formula

    Removed by JT
    Last edited by JohnTopley; 02-03-2016 at 04:24 PM.

  3. #3
    Registered User
    Join Date
    04-20-2005
    Posts
    48

    Re: Multi -array / -table Price Calculation Formula

    Thanks, that works! ...but think I made my example too simplistic in the sense the arrays will not always be the same size. If I try to adapt it to this set, I get an error.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Using your same structure, I use the formula (and get #N/A): =SUMPRODUCT((G1:G5=A1:A3)*(H1:H5)*(B1:B3)*(C1:C3))

    Different sized arrays unfortunately to use SUMPRODUCT. Apologize for my poor first dataset example.

    -JC

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: Multi -array / -table Price Calculation Formula

    And what about the first table- unlimited entries?

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: Multi -array / -table Price Calculation Formula

    The simple row by row calculation:

    =VLOOKUP($A1,$G$1:$H$3,2,)*B1*C1

  6. #6
    Registered User
    Join Date
    04-20-2005
    Posts
    48

    Re: Multi -array / -table Price Calculation Formula

    Quote Originally Posted by JohnTopley View Post
    And what about the first table- unlimited entries?
    The first table is dynamic on the number of entries, the second table is fixed. The size of the first will never exceed the size of the second (always a subset or complete set).

    Quote Originally Posted by JohnTopley View Post
    The simple row by row calculation:

    =VLOOKUP($A1,$G$1:$H$3,2,)*B1*C1
    Yep, I can do it row by row...but was hoping for a single cell calculation rather than running subtotals along the way.

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: Multi -array / -table Price Calculation Formula

    As far as I can judge array formulas- a likely solution - require consistent array sizes (as my attempted but wrong SUMPRODUCT proved).

    Some form of lookup is required (VLOOKUP or INDEX/MATCH) but I don't know how, or if, they can be incorporated in the required way.

  8. #8
    Registered User
    Join Date
    04-20-2005
    Posts
    48

    Re: Multi -array / -table Price Calculation Formula

    Quote Originally Posted by JohnTopley View Post
    As far as I can judge array formulas- a likely solution - require consistent array sizes (as my attempted but wrong SUMPRODUCT proved).

    Some form of lookup is required (VLOOKUP or INDEX/MATCH) but I don't know how, or if, they can be incorporated in the required way.
    ...was hoping some sort of SUMIFS or other function that could handle it. Thanks again; may have to eventually give up on a single formula. -JC

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: Multi -array / -table Price Calculation Formula

    With a "helper column" containing the VLOOKUP e.g in D in the formula below

    =SUMPRODUCT((B1:B4)*(C1:C4)*(D1:D4))

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 06-15-2015, 01:48 AM
  2. [SOLVED] Multi Calculation from start row/end row formula?
    By davidpierce in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-13-2015, 10:03 AM
  3. Replies: 9
    Last Post: 11-25-2014, 06:07 PM
  4. [SOLVED] Formula Calculation from multi cell to 1
    By hecgroups in forum Excel Programming / VBA / Macros
    Replies: 49
    Last Post: 03-04-2012, 11:18 AM
  5. Multi-cel array formula not working
    By sunilmulay in forum Excel General
    Replies: 4
    Last Post: 06-22-2009, 08:46 AM
  6. Price function difference in Output formula vis a vis Manual Calculation
    By abhi_23 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-17-2006, 03:57 AM
  7. Sum Array Formula Across Multi Sheets
    By SMS - John Howard in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-17-2005, 10:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1