MACRO OR FORMULA NEEDED FOR CALCULATION

K

K

Budget 17000

A C
E F-----------------COLOUMNS
Code Estimate Spending Actual Spendings Balance-----------
HEADINGS
1400 1500
G54 130
155
STU 1200 400
563 500
1000
HJ4 700
965 600
500

REQUIREMENT
I want "Macro" or "Formula" which can calculate Balance in Coloumn "F"
cells

CALCULATION METHOD SHOULD BE
As above mentioned that Budget is 17000 so in balance coloumn the
Macro or
Formula should minus first "Actula Spendings" figure from the Budget
and if there is no figure
in any cell of "Actual Spendings" coloumn then it should minus figure
of same Row cell in
"Estimate Spendings" coloumn. In other words it should always prefer
minusing figures
from "Actual Spendings" coloumn first and if there is no figure in
"Actual Spendings"
coloumn then it should minus figure from "Estimate Spendings" Coloumn.
And also if there
any code been put in "Code" coloumn cells then a text of "NOT RELATED"
should appear
in same row cell of "Balance" coloumn.

Please if you have any problem understanding above Question i have
also saved my excel file
in savefile.com where you can upload your files. Please see the link
below if you want to see the spreadsheet for more understanding.

http://www.savefile.com/files/1350632
 
K

K

Budget  17000

  A                          C
E                         F-----------------COLOUMNS
Code        Estimate Spending    Actual Spendings           Balance-----------
HEADINGS
                1400            1500
G54             130
                155
STU             1200            400
                563             500
                1000
HJ4             700
                965             600
                500

REQUIREMENT
I want "Macro" or "Formula" which can calculate Balance in Coloumn "F"
cells

CALCULATION METHOD SHOULD BE
As above mentioned that Budget is 17000 so in balance coloumn the
Macro or
Formula should minus first "Actula Spendings" figure from the Budget
and if there is no figure
in any cell of "Actual Spendings" coloumn then it should minus figure
of same Row cell in
"Estimate Spendings" coloumn.  In other words it should always prefer
minusing figures
from "Actual Spendings" coloumn first and if there is no figure in
"Actual Spendings"
coloumn then it should minus figure from "Estimate Spendings" Coloumn.
And also if there
any code been put in "Code" coloumn cells then a text of "NOT RELATED"
should appear
in same row cell of "Balance" coloumn.

Please if you have any problem understanding above Question i have
also saved my excel file
in savefile.com where you can upload your files.  Please see the link
below if you want to see the spreadsheet for more understanding.

http://www.savefile.com/files/1350632
Click to expand...

sorry as top data gone bit funny i have tried to show it again (please
see below)

Budget 17000

A C
E F
Code Estimate Spending Actual Spendings Balance
1400 1500
G54 130
155
STU 1200 400
563 500
1000
HJ4 700
965 600
500
 
S

Sandy Mann

If I follow you correctly and with the Budget figure in C1, the headers in
A2:F2 and the 14000/15000 in Row 3 then in F3 try:

=IF(COUNT(C3,E3)>0,C1-IF(E3<>"",E3,C3),"")

then in F4:
=IF(COUNT(C4,E4)>0,F3-IF(E4<>"",E4,C4),"")

and drag down as far as required.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Budget 17000

A C
E F-----------------COLOUMNS
Code Estimate Spending Actual Spendings Balance-----------
HEADINGS
1400 1500
G54 130
155
STU 1200 400
563 500
1000
HJ4 700
965 600
500

REQUIREMENT
I want "Macro" or "Formula" which can calculate Balance in Coloumn "F"
cells

CALCULATION METHOD SHOULD BE
As above mentioned that Budget is 17000 so in balance coloumn the
Macro or
Formula should minus first "Actula Spendings" figure from the Budget
and if there is no figure
in any cell of "Actual Spendings" coloumn then it should minus figure
of same Row cell in
"Estimate Spendings" coloumn. In other words it should always prefer
minusing figures
from "Actual Spendings" coloumn first and if there is no figure in
"Actual Spendings"
coloumn then it should minus figure from "Estimate Spendings" Coloumn.
And also if there
any code been put in "Code" coloumn cells then a text of "NOT RELATED"
should appear
in same row cell of "Balance" coloumn.

Please if you have any problem understanding above Question i have
also saved my excel file
in savefile.com where you can upload your files. Please see the link
below if you want to see the spreadsheet for more understanding.

http://www.savefile.com/files/1350632
Click to expand...

sorry as top data gone bit funny i have tried to show it again (please
see below)

Budget 17000

A C
E F
Code Estimate Spending Actual Spendings Balance
1400 1500
G54 130
155
STU 1200 400
563 500
1000
HJ4 700
965 600
500
 

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

Similar Threads

NEED MACRO OR FORMULA 4
NEED MACRO TO GET BALANCE 2
MACRO FOR DELETING DEBIT AND CREDIT FIGURES ROWS 1
MACRO TO DELETE ROWS 5
NEED MACRO FOR COLOUR BY CONDITIONAL FORMATTING 9
Please Macro or Formula Help needed 18
AMENDMENT IN MACRO 2
NEED CORRECTION IN MACRO 2

Top