NEED MACRO OR FORMULA

[K]

Hi, i am a bit confused doing some calculation for my work. i have
explained below the data i have on my spreadsheet. if anyone can
possibly send me Macro or Formula it will be much appreciated. you can
also see my spreadsheet by clicking on the link down at the bottom for
more understanding.

In cell B1 i have Budget figure of 17000
in row 2 coloumn A , C , E and F i have headings
the headings are
"Code" in cell A2 , "Estimated Spendings" in cell C2 , "Actual
Spendings" in cell E2 and
"Balance" in cell F2
I have data below the headings
Estimated Actual
Code Spendings Spendings Balance
3 1400 1500
4 G54 130
5 155
6 STU 1200 400
7 563 500
8 1000
9 HJ4 700
10 965 600
11 500

I want formula or macro in "Balance" coloumn which give balance figure
by minusing "Actual
Spendings" coloumn figure first from the Budget 17000 and if there is
no figure in "Actual Spending" coloumn cells then it should minus
figure from "Estimated Spendings" coloumn. (in other words it should
always prefer first minusing figure from "Actual Spending" coloumn and
then if there is no figure in this coloumn it should minus figure from
"Estimated Spendings"
coloumn. And also if there is any code in "Code" Coloumn then it
should not minus those figure
which come in code cell row. Like as above mentioned code G54 then
there is only one figure
of 130 in that row which should be not minus.

Please for more detail you can see my spreadsheet
http://www.savefile.com/files/1350632

 

[paul.robinson]

Hi,  i am a bit confused doing some calculation for my work.  i have
explained below the data i have on my spreadsheet. if anyone can
possibly send me Macro or Formula it will be much appreciated. you can
also see my spreadsheet by clicking on the link down at the bottom for
more understanding.

In cell B1 i have Budget figure of 17000
in row 2 coloumn A , C , E and F i have headings
the headings are
"Code" in cell A2 , "Estimated Spendings" in cell C2 , "Actual
Spendings" in cell E2 and
"Balance" in cell F2
I have data below the headings
                   Estimated    Actual
       Code     Spendings   Spendings  Balance
3                   1400          1500
4      G54       130
5                   155
6      STU       1200          400
7                   563            500
8                   1000
9      HJ4        700
10                  965           600
11                  500

I want formula or macro in "Balance" coloumn which give balance figure
by minusing "Actual
Spendings" coloumn figure first from the Budget 17000 and if there is
no figure in "Actual Spending" coloumn cells then it should minus
figure from "Estimated Spendings" coloumn. (in other words it should
always prefer first minusing figure from "Actual Spending" coloumn and
then if there is no figure in this coloumn it should minus figure from
"Estimated Spendings"
coloumn. And also if there is any code in "Code" Coloumn then it
should not minus those figure
which come in code cell row. Like as above mentioned code G54 then
there is only one figure
of 130 in that row which should be not minus.

Please for more detail you can see my spreadsheethttp://www.savefile.com/files/1350632

Hi
Try this in your balance column, cell F3

=IF(A3<>"",$B$1,$B$1-IF(E3=0,C3,E3))

Fill down for the other cells.
regards
Paul

 

[K]

Hi
Try this in your balance column, cell F3

=IF(A3<>"",$B$1,$B$1-IF(E3=0,C3,E3))

Fill down for the other cells.
regards
Paul- Hide quoted text -

- Show quoted text -

sorry friend its not working. if you see the spreadsheet then i think
you'll understand more what i am trying to say

 

[paul.robinson]

sorry friend its not working. if you see the spreadsheet then i think
you'll understand more what i am trying to say- Hide quoted text -

- Show quoted text -

Hi
My college blocks your site. Why does it not work - what do you see? I
put the data in the cells you specified.
Paul

 

[Dave D-C]

[hey, we're supposed to top-post in this group!]

It seems that Paul's formula is exactly right.What is wrong with it?
Why don't you post the missing(?) info here
instead of savefile.com? Dave D-C