Excel Busn Math 51: Convert Markups & Perishable Sell Price

Hi guys,

This is too complicated for me, so i am asking anybody's help to formulate Excel formulas to obtain the average buy price and average sell price for me to do this futures trading. Thanks a lot. I downloaded the Htmlmaker to post the spreadsheet here to show the manual way to calcualte the average buy price and average sell price but when it is on html form, i clicked on the 'Please click this button to send the source into clipboard' button & then i paste into this thread. Is the way to make my spreadsheet appear here correct cause it cannot work. Please help.

is there an easy way to do this??? I am trying to convert the RATE() function to a normal math formula, so I can calculate it within another program... Here is the example I am trying to convert to normal math B1 is the function.

Microsoft Excel - Book1 ___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout B1 B6 =
A B C D 1 Interest Rate 7.00% 2 3 Total Interest 279,017.80 4 # Years in Loan 30 5 # Payments / Year 12 6 Total Payments 360 7 Beginning Principal 200,000.00 8 Ending Balance 0 Sheet2

The sum up to the nth cost of a property that increases by 10% is calculated with this formula: Code:

=originalcost*purchase*(1+(purchase-1)/20)

I'm having trouble coming up with the formula if the cost doesn't start at the 1st property.

I thought replacing the 1st cost with the current cost would work, but it didn't. This formula is suppose to calculate bulk purchase cost.

I need help figuring a math formula to figure my price.

I have been doing it in my head somehow but now I am creating a website and I need a formula.

Right now I look at the cost versus the Retail price and see the amount of difference. My price needs to beat the Retail price so I look at the difference between cost and Retail price and then figure the least amount of discount I can give the customer that would be a good deal for them and me.

Example
A. Cost: $43.05
B. Retail Price: $238.99
C. My Price: $180.00
D. Customer Saved: $58.99
E. Profit: $136.95

I need a formula that is dynamic. When there is lots of difference between Cost and Retail I give a bigger discount but when there is not as much difference I give a smaller discount.

Here is a link to more data: http://www.box.net/shared/oyxchhpv3a

Thanks

Matt

Hi:

On April 14th, 2009 Sal Paradise posted a very interesting calculation to determine the Number of Remaining Shares in a Stock Portfolio which tracks "buys" and "Sell".

You can see his response to "Calculation on Buy and Sell". It is Post #1774. Doing a Forum Search, you can find it by entering, Calculation on Buy and Sell

A B C D E F G H I J 5 Trade No. Stock code Date time of trade Number of shares Buy price Sell price Trade type No. of remaining shares Cost Sale 6 1 ABN 140 200 Buy 140 28000 0 7 3 ABN 30 201 Sell 110 6000 6030 8 6 ABN 80 299 Sell 30 16000 23920 9 11 ABN 30 199 Sell 0 6000 5970 10 13 ABN 20 226 Buy 20 4520 0 11 2 BAN.C 100 1360.5 Buy 100 136050 0 12 8 BAN.C 30 1207 Sell 70 40815 36210 13 9 BAN.C 70 1270 Sell 0 95235 88900 14 15 HAN 40 33.5 Buy 40 1340 0 15 18 HAN 10 34 Sell 30 335 340 16 20 HAN 10 39 Buy 40 390 0 17 4 RAS 70 5202 Buy 70 364140 0 18 7 RAS 50 4908 Sell 20 260100 245400 19 12 RAS 20 4361 Buy 40 87220 0 20 5 SNE 100 650 Buy 100 65000 0 21 10 SNE 20 500 Sell 80 13000 10000 22 14 SNE 30 490 Sell 50 19500 14700 23 16 SNE 40 500 Sell 10 26000 20000 24 17 SNE 20 599 Buy 30 11980 0 25 19 SNE 20 480 Sell 10 11980 9600
I like the formulas he proposed and they work very well.

I need need to determine or figure out what the "Average Cost" of the No. of Remaining Shares"?

So, by his spreadsheet example, he has 20 shares of ABN remaining; however, he bought ABN shares at two different prices, $200 and $226.

What if you had paid several different prices for several different amounts (lots), what formula would you use to calculate the average cost price for the remaining shares?

Once again, I cannot past the spreadsheet here for you to take a look at it; but you can see both, the Spreadsheet and his proposed set of formulae by doing a quick Forum Search on "Calculation on Buy and Sell", or look at Post #1774

I'd be very happy and appreciative if anyone could provide me with a solution to determing the "Average Cost" of the No. of Remaining Shares"?

Many, many thanks in anticipation.

Cheers!

I have to credit the Air Force with this one.

We have a spreadsheet I get from them everyday containing times they request support in ZULU time, so it reads like 0700z in the cell and the end time they requested the support till sorta like 0900z.

I need to convert that time and subtract it so that I can add it up later.

Right now I have a column over to the right that converts 0700z to 07:00 and 0900z to 09:00 then a column that subtracts them and then one that breaks them down into minutes so I can do a formula elsewhere that counts all of the hours they requested.

Can all this be automated since it is not going to be possible to get these guys to change their stuff.

Hello all. Thought I could find the answer to this in the forum and even tried the Internet, but my search terms either crashed the search function or the results were of little help.

I have 3 formulas I currently copy down on the sheet:

Code:

=MAX(0,E8-$B$5)

Code:

=(E8-D8)-K8

Code:

=(E8-D8)*F8

The code block I tried for the first formula is:

Code:

    'Formula insertion G
If Not Intersect(Cell, Range("E:E")) Is Nothing Then
    With Cell.Offset(, 2)
        .FormulaR1C1 = "=MAX(0,R1C1-$B$5)"
        .Value = .Value 'to override formula and leave just result
        .HorizontalAlignment = xlRight
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End If

I tried

Code:

.FormulaR1C1 = "=MAX(0,Cell-$B$5)"

as well as few other variations but to no avail. If someone can help with the above code block, I should be able to add 2 more code blocks for the other 2 formulas since they are all similar.

The above block checks to see if there is a date in column E. If there is a date in E, then the formula performs a simple subtraction of the date in

Code:

$B$5

from the date in column E and places the result in column G.

I didn't think it would be this difficult but I can't seem to grasp formulas written in VB.

Any help would be appreciated.

Thanks,
Andrew

I'm looking for an Exel formula that allows multiple markups. For instance: If a Change Order is between $1 - $1,000, I'm allowed a 10% markup; if the Change Order is between $1,001 and $10,000, I'm allowed 10% on the first $1,000 and 7% on the balance; and finally if the Change Order is $10,001 or more, the preceeding applies and anything over $10,001 is at 5%.

Is there anything beside the AND and IF stuff that wouldn't be a formula a mile long?

Hello,
How do I get Excel to NOT show the Markup's when I launch a spreadsheet?

By default it always loads on some spreadsheets and I don't want to always have to select View > Markup to remove the viewing of the Markup's.

Thank you
The Muffin Man

hi
i am a new member in this forum
i have a question ...
i desgined 2010 world cup schedule on excel ... with the quilified teams
i will fininsh it 100% on december

i want to know how can i sell it ,, and where ??
i am wating for answers
thanks