Windows 7 Forums

Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.


Windows 7: Excel Formula Help please...

20 Nov 2010   #1
BomberAF

Windows 7
 
 
Excel Formula Help please...

This is probably very simple and I have got it working but it isn't working how I want it to work.

I have created an Excel document to show my monthly payments of a bank loan. I have created 3 columns in the first column is the payment number, in the second column is the amount that the loan is after I have made a payment, and in the third column I want to know how much the loan has been reduced by after the bank has taken interest off.

I have just put this formula in the 3rd column, =SUM(D4-D5), and then dragged this formula down into all the other rows I want this to work in which it does. The problem I am having with this is that the number from the 2nd column get placed in the 3rd row, below the amount that has been reduced, and I would like it to stay at 0.00 as it looks better.

Here is what I mean




If you notice the number I enter in the balance column appears in the reduced by column, and I don't want it too because it looks very unprofessional.

Can someone please help me out with this please.


My System SpecsSystem Spec
.
20 Nov 2010   #2
Bill2

Windows 7 x64 pro/ Windows 7 x86 Pro/ XP SP3 x86
 
 

Try using IF ISBLANK.

So, the formula could be something like

=IF(ISBLANK(D5),"",SUM(D4-D5))
My System SpecsSystem Spec
20 Nov 2010   #3
BomberAF

Windows 7
 
 

Quote   Quote: Originally Posted by Bill2 View Post
Try using IF ISBLANK.

So, the formula could be something like

=IF(ISBLANK(D5),"",SUM(D4-D5))

Thanks for that it worked a treat, now I know I am being fussy, but all the cells that had 0.00 in the rest of the spreadsheet have just gone blank.

How can I get the cells to have 0.00 in them again, but keep the value from being carried?
My System SpecsSystem Spec
.

20 Nov 2010   #4
Bill2

Windows 7 x64 pro/ Windows 7 x86 Pro/ XP SP3 x86
 
 

Try this.

=IF(ISBLANK(D5),0,SUM(D4-D5))

Otherwise, you could insert a string

=IF(ISBLANK(D5),"0.00",SUM(D4-D5))

You may need to adjust the formatting etc.
My System SpecsSystem Spec
20 Nov 2010   #5
BomberAF

Windows 7
 
 

Quote   Quote: Originally Posted by Bill2 View Post
Try this.

=IF(ISBLANK(D5),0,SUM(D4-D5))

Otherwise, you could insert a string

=IF(ISBLANK(D5),"0.00",SUM(D4-D5))

You may need to adjust the formatting etc.

Once again many thanks that was very helpfull I have learned something today, just wish I could rep you more but alas I can only rep you once, if I see you around I will give you some more.
My System SpecsSystem Spec
20 Nov 2010   #6
Irene

Windows 7 Home Premium 64bit
 
 

Another possible is...


You would of course need to drag the formula down through the rest of the column.


Attached Images
 
My System SpecsSystem Spec
20 Nov 2010   #7
Irene

Windows 7 Home Premium 64bit
 
 

Quote   Quote: Originally Posted by Irene View Post
Another possible is...


You would of course need to drag the formula down through the rest of the column.
This formula basically says:
If the number in column 'C' is more than zero, take C3 from C2, otherwise enter zero in the column 'D' cell.
My System SpecsSystem Spec
Reply

 Excel Formula Help please...




Thread Tools






Similar help and support threads
Thread Forum
Excel 2013 - how to keep a formula consisten
Hi I am trying to tie one cell into a formula that is being pasted into multiple rows. I know you can use $ to keep the cell consistent but it doesn't seem to be working. So if the value is in c9 and then first row gets c9 then the new row gets c10 etc... I need it to be c9 throughout the...
Microsoft Office
Excel Formula
Hello Sir, Can you let me know the formula in Excel: I have a table as below: This table, At the column "A,Status" when i Put the word" Completed", it's will automatic put current Date to Column "B, Row 2". But i don't know after that when tomorrow arrived,at the column "B,Row 2",...
Microsoft Office
Excel 2003 sum formula problem
I have been entering currency data in Column B in Euros to an EXCEL 2003 s/s and the formula used has added these totals entered in that Column correctly until I reached a total sum of 74364.70 Euros ,this being the sum of entries in Rows B6 to B82 . This was reached with the formula showing as...
Microsoft Office
Want to copy data and formula from one Excel Sheet to another.
I have a budget workbook set up in Excel, and in that workbook there are many different sheets all part of the one book. I have created a formula in one sheet from the data that is collected there, and that data is important for information collected on another sheet. I want to copy the...
Microsoft Office
excel syntax problem for formula... difference in days
if I have 2 columns of dates and I want to find the number of days between the days of D2 and C2, what is the syntax for the formula so that the result in column E is x number of days between the two dates
Microsoft Office
Cant scroll while editing formula Excel 2007
Hi I'm unable to scroll while editig a formula in excel 2007. i know it can be done if i edit directly in cell but i prefer editing in the formula bar. I've seen an apparent solution in some forum which says change the mouse setting in the control panel (the wheel tab). Start --> Settings -->...
Microsoft Office


Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd

All times are GMT -5. The time now is 19:23.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App