Forum Home
Press F1

Thread ID: 27058	2002-11-12 09:32:00	Excel Formulas	Happy Harry (321)	Press F1

Post ID	Timestamp	Content	User
98088	2002-11-12 09:32:00	Hi All I started out with what I thought would be a simple project. A nice little spreadsheet to help out a budget oriented student, but I have dug myself a big hole Want I basically have is a 4 column spreadsheet labled "Weekly" "Fortnightly" "Monthly" & "Yearly" I wish to be able to fill in a figure in any of the 4 columns and extrapolate the correct figures into the other 3 columns i.e If I have $20.00 in the Fortnite column, divide this by 2 to get the weekly figure, multiply the fortnite figure by 26 and divide by 12 to get the monthly figure and so on. I've tried using all sorts of formulas and lots of "if" and "then" statements but ended up with a mess. I want to be able to put an amount into any of the 4 columns and have the other 3 calculated out and filled in. I'm sure that somebody a lot more clever than me has already done this.. Can any body point me in the right direction. Cheers and thanks in anticipation Happy Harry	Happy Harry (321)
98089	2002-11-12 09:44:00	You would have nested formulas and an immediate circular reference. Each column would need to depend on the others, which in turn depend on it.....any spreadsheet will get bitter and twisted on that. You need a dynamically assigned formula dependent on which column gets filled in, a bit beyond basic excel really. Its possible using 4 external cells/columns I guess, with tests for which entry cell is non-zero, but why would you want to get so complex for a manually entered budget?	godfather (25)
98090	2002-11-12 09:47:00	An easier method would be a single column for the data entry, and a second column for the "period", being 1,2,4 weekly etc. That can immediately extrapolate out to your main sheet.	godfather (25)
98091	2002-11-12 09:54:00	One way to do it is to create 6 columns. The first contains your figure - $20, the second a letter - w, f, m, y Then if statements in the other 4. Along the lines of "IF B2="w", A2,0" in the first cell. Haven't checked the exact syntax but this would avoid the circular reference.	Heather P (163)
98092	2002-11-12 19:17:00	Or, you have the input columns for the values and the calculated columns which each work based on where the number was entered, but you are looking at four nested if statements and it doesn't sound worth it. Can't you just convert all values to weekly and input in the weekly column? A monthly bill of $65 for Sky Digital would go in as =65*12/52, and fortnightly bill for $300 rent would be =300/2 (if the person entering didn't already know the answer) and an annual cost would go in as =375/12. robo.	robo (205)
98093	2002-11-12 20:13:00	I highly recommend having a personal budget .. and I would love to "see" your result My approach has been to create a spreadsheet to record ALL bank transactions - virtually a 'mirror' of the bank statements, with columns for date,2x description, amount, balance. As I have several accounts I duplicate columns for description, amount and balance and one last column is for a total of all accounts. The calculations thus are simple BUT it requires a fare bit of copying and modifying lines each year when I extend it for the new year Simple to set up and then go back and fill in the budgeted amounts and zooming down to the end of the year to see the result on the bank balance = yay, an overseas holiday is affordable, or that new pc for xmas or whatever The business one is a workbook with a sheet per company but several columns for the different types of transactions, the columns are added through to a balance column. Set up one then copy the worksheet to create the next, etc. THEN go through them and fill in the estimates. There is also a sheet which aggregates the figures form the same cells on each other sheet to give a group total sheet.. And there are fortnightly subtotal rows which are duplicated in a summary at the bottom , with graphs of the fortnightly subtotals ... Lots of fun	Woof (2402)
98094	2002-11-12 21:47:00	Thanks Guys for all the input. You've given me a few more ideas to work from. I'll see if I can get some thing to work from here. Will let you know how it goes Cheers Happy Harry	Happy Harry (321)
98095	2002-11-13 22:44:00	Happy Harry, 10 years ago I would have been able to do it blindfolded. The best thing that ever happened in the accounting world was the spreadsheet, you don't need a rubber (the school type) to change the figures, just type in the revised figure. Weekly 100.00(1) 200.00 433.33 5,200.00 Fortnight 100.00 200.00(2) 433.33 5,200.00 Monthly 100.00 200.00 433.33(3) 5,199.96 Annual 100.00 200.00 433.33 5,200.00(4) Is A1 +A12 +A152/12 +A152 +B2A1 Is B2 +B252/12 +B226 +C312/52+C312/26 Is C3 +C3*12 +D4/52 +D4/26 +D4/12 Is D4 Never had to do this type of budget. Good luck Les #	leshibbard (873)
1