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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 05:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com