# Help me reduce 30 mb file size

1. ## 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.

2. ## 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. ## 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. ## Re: Help me reduce 30 mb file size

Originally Posted by DonkeyOte
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. ## 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. ## Re: Help me reduce 30 mb file size

Originally Posted by DonkeyOte
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. ## 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!

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

#### 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