Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
NEED MACRO OR FORMULA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
NEED MACRO OR FORMULA
On Jan 31, 8:59*am, K wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
NEED MACRO OR FORMULA
On 31 Jan, 09:38, wrote:
On Jan 31, 8:59*am, K wrote: 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- 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
NEED MACRO OR FORMULA
On Jan 31, 9:51*am, K wrote:
On 31 Jan, 09:38, wrote: On Jan 31, 8:59*am, K wrote: 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- 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- 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
NEED MACRO OR FORMULA
[hey, we're supposed to top-post in this group!]
It seems that Paul's formula is exactly right. =IF(A3<"",$B$1,$B$1-IF(E3=0,C3,E3)) What is wrong with it? Why don't you post the missing(?) info here instead of savefile.com? Dave D-C wrote: sorry friend its not working. if you see the spreadsheet then i think you'll understand more what i am trying to say On 31 Jan, 09:38, wrote: 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 - On Jan 31, 8:59*am, K wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I add a formula to a macro? | Excel Discussion (Misc queries) | |||
Formula in macro causes macro to fail | Excel Programming | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
Formula & Macro | Excel Programming | |||
Do I need a formula or Macro? | Excel Worksheet Functions |