# Solved: Can someone help me with this Excel spreadsheet?

Discussion in 'Business Applications' started by jmk909er, Dec 4, 2013.

Not open for further replies.

Joined:
May 20, 2009
Messages:
354
I have uploaded a sample of the spreadsheet I am trying to modify and I kinda have it working the way I want and I need these costs broken out the way I have them. I will try to explain what I want the best I can so bear with me.

I want "Total" to capture if it is an expense or income. (right now it is not)

I want "Balance" to reflect if it is an expense or income. (right now it is not)

I want any unpopulated cells to show nothing or ""

This is probably simple for an excel wizard but I only know excel good enough to get into trouble!

Any help is appreciated, thanks, Joe

File size:
18.3 KB
Views:
30
2. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,180
First Name:
Wayne
sorry not really sure what you want to do on the spreadsheet - can you show on a sheet wjat you would like 2013 sheet to actually look like ?

apart from row 14 , i see no income

Joined:
May 20, 2009
Messages:
354
Hi, sorry I did not give a very good example I have uploaded a better example of row 14. This is what I am after but I did the math manually to increase the balance.

File size:
18.4 KB
Views:
33
4. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,180
First Name:
Wayne
you can use
=IF(SUM(B2+D2+E2)=0,IF(C2="","",C2),SUM(B2+D2+E2))
in F2 and copy down - this will add the income
BUT the balance , I do not know how you have \$2,193.06

you need to start the sheet with whatever balance is available from previous years and
if you put that in the first row as an income
then you could use
=SUM(\$C\$2:C2)-SUM(\$B\$2:B2)
this will add up all the income and subtract the expense put into I2 and copy down

5. ### CodeLexicon

Joined:
Oct 15, 2013
Messages:
503

What I feel you need to do is to sack the idea of multiple sheets for years and instead. Work with a single ordered datasheet. This makes it much easier to analyse and you could use a pivot table for example. Or, as I've done, add some basic summary data to the top of the sheet. The charts etc all work on the number of IDs ... so as you add more, the chart will adapt, as will the calculations.

You're supplying a date (which makes year sheets unnecessary) and now you explicitly state something is an expense or income item. You also get into the habit of putting expense items as debits (\$-) and income as credits (\$+). That way all your running balance has to do is to add the latest credit or debit from the previous line's running balance. The formula I have used is not a straight sum, because if you were to use a line above + left type sum you'd have difficulties if you sorted the datasheet in a different order. The formula I use looks for the ID number preceding the current line and then adds the "Total" (+/-) form the current line.

Anyway, it's just a thought. It looks like you're just embarking on this project, so I thought a different view at this stage might be beneficial.

#### Attached Files:

• ###### income-expenses-tester-v2_CL.xlsx
File size:
24.5 KB
Views:
36

Joined:
May 20, 2009
Messages:
354
Wow Codelexicon you are awesome! Way more than I expected. This is way advanced for me...can you tell we what this is doing in J column?: =INDEX(\$J:\$J,MATCH((A26-1),\$A:\$A,0))+I26

I would like to be able to under stand it a little bit.

7. ### CodeLexicon

Joined:
Oct 15, 2013
Messages:
503
Well, that formula is just:

checking for the ID from the current record (in column A) and taking away 1 to get to the previous record and then (from column J, returning the Running Total + the latest (+ or -) Total (from column I)

Chandoo is good at explaining it ...

Joined:
May 20, 2009
Messages:
354
Thanks so much, you have inspired me and I am creating and adapting into exactly what I want. I am trying to populate some data. Is there a way to convert a column of costs like \$100.00 to -\$100.00 or do I need to do this one at a time?

9. ### CodeLexicon

Joined:
Oct 15, 2013
Messages:
503
Sure - there's a simple way

type -1 in a spare cell then select that cell > copy

select the range you need to convert

select paste special > values operation should be Multiply

click "OK"

Joined:
May 20, 2009
Messages:
354
It worked great. It populated the empty cells though with 0.00 is there a way to find and replace with "" so that those stay blank?

11. ### CodeLexicon

Joined:
Oct 15, 2013
Messages:
503
You can use conditional formatting to do that ... I did it in the file i uploaded. Have a look at the rules.

Joined:
May 20, 2009
Messages:
354
Is there a way to make the cells under "TYPE" have a drop down for INCOME and EXPENSE to populate the cells?

13. ### CodeLexicon

Joined:
Oct 15, 2013
Messages:
503
Sure

Somewhere in the sheet, or on another sheet, type the two words Expense and Income, one above the other.

Then, select the cells where you want the drop down to be.

Go to Data > Data Validation

Allow "List"
Source - select the icon next to the text input box, select the two cells you've just put Expenses and Income in and hit enter

Now, you should have a drop down choice of Expenses or Income

Joined:
May 20, 2009
Messages:
354
Hey CodeLexicon, everything is working beautifully! You have been such a great help. I am going to mark this as "Solved" Thanks Again!

Joined:
Oct 15, 2013
Messages:
503

As Seen On