calculating wanted cash from overlimit dealing in stock market.

W

Wafa

Bismillah

Hi everyone, I have a little problem here with excel, I hope i can
find someone to help with it.

Now let us say that we have a portfolio that deals with more than on
broker, buying selling and so on, at the end of day we receive 2 or 3
reports from these brokers, and we discover that we used more cash than
we deposited in the stock market, how can we create a formula for the
following:




B/S Balance Wanted cash

(63,000.000) (63,000.000) (63,000.000)
12,600.000 (50,400.000) (63,000.000)
(40,000.000) (90,400.000) (90,400.000)
10,000.000 (80,400.000) (90,400.000)
12,000.000 (68,400.000) (90,400.000)
(80,000.000) (148,400.000) (148,400.000)


Now the day started short with 63,000 if there is more buying it will
accumulate but the moment we sell the wanted cash will be fixed, but if
we start buying again and the balance is more than the wanted cash as
seen then the wanted cash will be same as balance (90,400.000), and so
on and it will be fixed in the wanted cash and it will not change if
there is a selling like in the balance.

I used a formula which gave me the lowest amount in the balance which
is the wanted cash but this formula will fix the lowest amount from
cell one to the end. is there a way to fix the amount and add to it if
the balance is higher than wanted cash.

The formula i used was =MIN($F$7:$F$400) I want the result to appear
as in column 3

Help anyone :)

Thankyou in advance
 
J

joeu2004

Wafa said:
B/S Balance Wanted cash

(63,000.000) (63,000.000) (63,000.000)
12,600.000 (50,400.000) (63,000.000)
(40,000.000) (90,400.000) (90,400.000)
10,000.000 (80,400.000) (90,400.000)
12,000.000 (68,400.000) (90,400.000)
(80,000.000) (148,400.000) (148,400.000)

I think the formula for "Wanted Cash" can be one of the following
(assuming the first -63000 line is row 3 and "Wanted Cash" is column
C):

C3: =IF(A3<0, SUM($A$3:A3), C2)

C3: =if(A3<0, B3, C2)

Copy down column C as needed. The first formula allows you to avoid
the "Balance" column. But since I suspect the "Balance" column is
useful to you, the second formula is the more efficient and
straight-forward, IMHO.
 
W

Wafa

Bismillah
Thanku joe for your help I will try it and see if it turns okay and I
will let u know :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top