+ Reply to Thread
Results 1 to 7 of 7

Help me reduce 30 mb file size

  1. #1
    Registered User
    Join Date
    11-05-2009
    Location
    BH 90210
    MS-Off Ver
    Excel 2003
    Posts
    3

    Help me reduce 30 mb file size

    I have 3 variables that I am working with.

    Count (variable to be entered into a cell by the user)
    Cost (fixed @$30,000)
    Fee (fixed @ $3,000)

    Example
    Count / Cost / Running Total
    1/$30,000/$30,000
    2/$33,000/$63,000
    3/$36,000/$99,000
    4/$39,000/$138,000

    Each subsequent unit cost will rise by $3,000 (Fee)
    The running total takes into account what has been spent so far.

    I cannot simply say (Cost * 4) + ( Fee * 4 ) because that equals ($120,000) + ($12,000) = $132,000 which does not equal the $138,000 that it actually is.

    I am hoping someone can help because right know I have this manually entered in a separate tab with over 30,000 rows of data, and using VLOOKUP to return column values based on row number that equals the varible entered by the user and the file is over 30 MB.

    Thanks in advance

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Help me reduce 30 mb file size

    It's a sum of series type calc really regards the unit cost increment and, as always, I can't remember the basic math when I need to so below is a SUMPRODUCT approach

    =SUM($B$1*$B$2,SUMPRODUCT($B$3*(ROW(B$1:INDEX(B:B,$B1))-1)))

    however the basic math formulae should be used in preference... just need to remember it!

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Help me reduce 30 mb file size

    In math terms...

    Sum of Arithmetic Series would be

    Please Login or Register  to view this content.
    where

    a is first number in series - in my example this is B2 - 30000
    N is number of terms - in my example this is B1 (count)
    d is common difference of successive members - in my example this is B3 - 3000

    So your total can be calculated with

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    11-05-2009
    Location
    BH 90210
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Help me reduce 30 mb file size

    Quote Originally Posted by DonkeyOte View Post
    It's a sum of series type calc really regards the unit cost increment and, as always, I can't remember the basic math when I need to so below is a SUMPRODUCT approach

    =SUM($B$1*$B$2,SUMPRODUCT($B$3*(ROW(B$1:INDEX(B:B,$B1))-1)))

    however the basic math formulae should be used in preference... just need to remember it!
    Are you sure this will work?

    Let me try to better explain... I only want to use these three things.
    A1 = Where the user will input the count <--- Variable value
    B1 = $30,000 "Cost" <-- static value
    C1 = $3,000 "Fee" <-- static value not added in on 1st unit.
    D1 = B1+(C1*A1) "Next Unit Cost" <-- Calculates when user enters value into A1
    E1 = Running Total <-- Calculates when user enters value into A1
    Here are the Results I want:

    Next Unit Cost - This is easy enough figure out B1+(C1*A1)

    User enters 1 - Result is $30,000 + ($3,000*1) = $33,000
    User enters 2 - Result is $30,000 + ($3,000*2) = $36,000
    User enters 3 - Result is $30,000 + ($3,000*3) = $39,000
    User enters 4 -Result is $30,000 + ($3,000*4) = $42,000
    ----------------------------------------------------------
    (E1) = Running Total ???? UNKNOWN FORMULA I NEED

    Example 1: if the user enters a 2 into A1 then the formula needs to add B1 ($30,000) + the cost of 2nd building ($33,000) = $63,000 (E1)Running total.

    Example 2: if the user enters a 3 into A1 then the formula needs to add together B1 ($30,000) + the cost of the 2nd building ($33,000) + the cost of the 3rd building ($36,000)= $99,000 (E1) Running total.

    Example 3: if the user enters a 4 into A1 then the formula needs to add together B1 ($30,000) + 2nd building ($33,000) + 3rd building ($36,000) + 4th building ($42,000)= $138,000,000 (E1) Running total.

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Help me reduce 30 mb file size

    Why not actually test it ? I try to not make a habit of posing things that don't match up to the requirements...

    As I said, you are effectively conducting a sum of arithmetic series. The Math to calculate such a series is proven and is illustrated in post # 3, post #2 does the same thing but less efficiently.

  6. #6
    Registered User
    Join Date
    11-05-2009
    Location
    BH 90210
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Help me reduce 30 mb file size

    Quote Originally Posted by DonkeyOte View Post
    Why not actually test it ? I try to not make a habit of posing things that don't match up to the requirements...

    As I said, you are effectively conducting a sum of arithmetic series. The Math to calculate such a series is proven and is illustrated in post # 3, post #2 does the same thing but less efficiently.
    WOW!!! awesome thank you so much. I can't believe the formula is this small and efficient.

    follow up question..I would really like to learn more about this formula. When i google for it do i google on "sum of arithmetic series" ??? Or is there some official Excel term i should be using? I take it there is not a built in Excel function for this?

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Help me reduce 30 mb file size

    for info. on the math:

    http://en.wikipedia.org/wiki/Arithmetic_progression
    http://www.algebralab.org/lessons/le...rithSeries.xml

    in terms of calculating using built in functions... there may well be a math based function that can do this however that is not my area of expertise / business so I wouldn't know off hand I'm afraid - I just do things like this the old fashioned way!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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