Use IF To Avoid Getting Anything Written After

Nov 5, 2008

I'm havin' a chart for interest, instalments and so on;

Interest [X%] Loan [Z crowns/dollars]
Loantime [Y years] Instalment [formula, which is "=Z/Y/12"]for each month.

Year, Month, Loan(left), Instalment, Total payment
1, Januari, =Z, =Instalment (constant), =Interest+Instalment

Month, Loan(left), Interest
Februari, =above-Instalment, =X%*Loan(left)/12 (rent is divided per year),

Instalment, Total payment
Constant, =rent+Instalment

and so on... as long as you want ^^ (it's quite messy, but try :D)

The question is as follows "What would a loan of 900'000kr with 9% interest rate totally cost over 30 years?"

[use "IF" to avoid getting anything written after the loan expires]

The question itself can be easily solved by just dragging the formulas down to 30 years and fill in the % and loan etc. But what I do not understand is the "IF" question. I'm supposed to use the IF function to instantly show the cost of the loan (total rent payment) after the 30 years? That's how I understood it. In that case... how do I do that? ^^

View 14 Replies


ADVERTISEMENT

Written Number()

Feb 15, 2007

This is a a formula entered in a checkbook program for excel that I downloaded. But I want to use my current accounting spreadsheet to print checks. The problem is I don't know how to get excel to write out a number like $23.40 as:

Twenty Three & 40/100

Does anyone know how this is done? Is it something simple that I am overlooking - i can't find anything like this in the number formatting section of excel.

View 9 Replies View Related

Formula's Value Written To ClipBoard

Oct 4, 2009

I have a formula that will give me a phrase that I need to be able to paste into other applications - the value and not the "mid"s and the "search"s, etc. So, can anyone tell me how to take that formula's value and write it to the clipboard?

View 3 Replies View Related

Formula Written In Word

Jan 12, 2009

I wrote out a long IF statement in Word, but when I pasted in the formula bar, I got an error. I've determined that the quote marks are causing the error. Can anyone advice how to fix this going through and changing the quote marks one-by-one?

View 9 Replies View Related

Calculate Written Numbers

Dec 30, 2006

Whilst I was searching through the channels on telly the other night I came across a 'Quiz Call' channel where you had to add ALL the number within a certain sum (see sample) Sixteen minus five times seven multiply by nine add twelve

You not only have to do the sum you also have to add in all the less obvious numbers like the Six within Sixteen, plus also all the Roman numbers in each word like the I, X, & IX within the sixteen. I've done a small example to show what I mean, but I don't have any idea how to create a formula that will add up all of all of the permitations of roman numerals within each word.

View 9 Replies View Related

Way To A Column Be Filled Automatically By What Is Written On Others

Jan 15, 2009

Is there any way to a column be filled automatically by what is written on others columns.

Example: If one of the cells A2 or B2 and C2 are Nonblank, fills automatically the cell H2 with "ABC2" And If some of the cells D2 and E2 are Nonblank fills the cell H2 with "DE2"

View 2 Replies View Related

Can't Control Directory File Is Written To

Mar 1, 2014

The script below writes a ".csv" file to the same directory that contains the workbook the macro, "foo" is run from. I'd like to change the script to always write the file to a separate directory which is: "c:etcupload".

[Code] .....

View 1 Replies View Related

Macro Open The Worksheet In Which It Was Written

Sep 13, 2007

I've written a macro and i've added a button to the toolbar so that it's easy for users of the spreadsheet to run the macro. the problem i'm running into is that the sheet that the macro's written in is a template. the macro formats some downloaded junk. soooo, every time someone downloads stuff, they copy-paste into the template, push the button, and it formats it.

my problem is that when they Save As, the button's assigned macro gets reset to the name of the file they saved as. therefore, when someone else comes along and press the button, it doesn't run the macro out of the original template sheet which they have open, instead it tries to find the last sheet that was saved and run the macro through it.

View 10 Replies View Related

Add Text To Individual Cells Which Can Be Written Over?

Aug 9, 2013

Is it possible to add text to individual cells which can be written over?

e.g. I would like A1 to have the background text of "slot 1"... Once user has inserted text then "slot 1" is removed... and when the user text is removed, "slot 1" reappears.

View 2 Replies View Related

Modify VBA Code Written By Tom Urtis

Jan 22, 2008

I am using the following code, slightly modified, written by Tom Urtis.

I would like to enhance the code such that it will filter on two criteria to enable more specific results to obtained e.g. from the possibilities below I would like to get "Jones Manchester".

Data list:-
Jones Blackburn
Jones Liverpool
Jones York
Jones Manchester
Jones London EC1
Jones London N12

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address "$D$1" Or .Cells.Count > 1 Then Exit Sub
ActiveSheet.AutoFilterMode = False
If Len(.Text) > 0 Then
Dim myVal$, myVal2$
myVal = .Value
myVal = "*" & myVal & "*"
If WorksheetFunction.CountIf(Columns(1), myVal) > 0 Then

View 9 Replies View Related

Creating Written Summary In 2002

Sep 1, 2008

I am creating a financial summary on a worksheet and I want to be able to write up a summary about each category. Is there a way that I could write text on my worksheet and create a parameter where my text could go to the next line such as a word document?

View 9 Replies View Related

Reminder Statements Written In The Message Box

Feb 18, 2009

creating a MSG box to be placed at the start of the code, i need the following reminder statements written in the message box. If the answers to all statements is Yes then proceed running the code.

1. Is the value date in Sheet Summary Correct?
2. Is the workbook free of errors, check sheet "Errors"
3. Are all the graphs up to date?

Sub Save_File()
Application.ScreenUpdating = False
Sheets(Array("SUMMARY")).Copy
With ActiveWorkbook
.Sheets("SUMMARY").Cells.Copy
.Sheets("SUMMARY").Cells(1).PasteSpecial xlPasteValues
.SaveAs Filename:="S:Recsdata ReportingRec StatsSummary Stats" & Format(Date, "yyyy\MMM") & _
"Stat Summary_" & Format(Date, "dd-mm-yy") & ".xls"
.Close False
End With
Application.ScreenUpdating = True
End Sub

View 9 Replies View Related

Comparing Spreadsheet To Written Data

Feb 1, 2010

Is there anything in Excel 2007, the spreadsheet and written data are tangled lists of numbers and its hard work sorting it out? So something like highlight current row and darken others and tick each correct row with one click would help a lot.

View 10 Replies View Related

Pulling Data From Differently Written Names

Nov 4, 2013

I have a spreadsheet with numerous transactions that contain the same name but are often spelled differently or inconsistently. I would like to pull all the transactions with the similar name and in a new sheet, make all the inconsistent ones, the same e.g. (space)mr smith or mr smiths or mr smith's or mr smith would all be mr smith. I am thinking of using the match command as a possibility but want it to repeat each row.

I am attaching a spreadsheet to show the existing table and the desired result which is similar to what I am showing below.

Example:

Existing Names and data
A B C D E
1mr smiths xyz10020%
2 mr smith abc12430%
3mr smith's def20040%
4mr smith xyz7550%
5simon inc zbc14065%
6simon's inc xyz20070%
7simon abc9525%
8shows zbd18045%

Want it to show Names and data:

A B C D E
1mr smithxyz10020%
2mr smithabc12430%
3mr smithdef20040%
4mr smithxyz7550%
5simon inczbc14065%
6simon incxyz20070%
7simon incabc9525%
8showszbd18045%

View 2 Replies View Related

AutoFilter Based On Contents Written In A Cell

Aug 8, 2014

I'm trying to create an autofilter that can filter each category that gets listed in a specific cell.

I have some code from another sheet which I'm trying to modify to accept more than 1 filter criteria.

[Code] .......

Where W2 lists something like:

"Blasting, Flotation, Drilling" and I want it to filter all three categories in the location J24 to the last row with data in it.

I believe I need to apply "*" in locations but I'm not sure where?

View 4 Replies View Related

Macro To Run Code Written In Cells BA1 To BA2000

Sep 21, 2011

I wrote this code inside the excel spreadsheet itself. The code sits in BA1 to BA2000. I would then copy and paste this code to a VB module, then run it. As the data changes, the SchemeColor number changes. My question... is there a way to run write a macro in VB and have it use the code in cells BA1 to BA2000 so that I do not have to copy and paste it into the VB Module and run it? Manually running it is ok with me, but I do not want to copy and paste the code into VB each and every time.

Here is my code:
Sub Macro2_()
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Points(1).Select

[Code].....

View 2 Replies View Related

Group Range Of Cells According To Months Written In One Column

May 15, 2014

Sample workbook for Excel forum.xlsx

I have multiple lists like company A company B so on and on. each has multiple accounts maturing at different dates.how can I group accounts maturing in one particular month? the maturing dates are different in some accounts and same in others.

company A
account no maturity date
account no maturity date
company B
account no maturity date
account no maturity date
so company C, company D etc..

All are on different sheets, I tried to figure out with Vlook up, and filter, but could not make formula.

View 7 Replies View Related

Code To Delete Whats Written In A Range Of Cells

Oct 19, 2009

I use this code to delete whats written in a Range of cells ...

View 6 Replies View Related

VBA Code Overwriting Data Written In Previous Loop

May 9, 2013

I have amended the code below and have got it working. The problem I have now is that every time it loops it overwrites the data it wrote the previous loop

Offending line being ActiveSheet. Range ("A1: D30") = ValuesArray

I have known that somehow it should remember the last row and copy below this one but I cannot get it to work

Code:
Sub Basic_Example_1()
Dim MyPath As String, FilesInPath As String
Dim MyFiles() As String
Dim SourceRcount As Long, Fnum As Long
Dim mybook As Workbook, BaseWks As Worksheet
Dim sourceRange As Range, destrange As Range

[Code] ..........

View 1 Replies View Related

Return All Row Numbers That Have Given Value Written Somewhere Inside Text In Column B

Jul 24, 2013

By "Return all row numbers that have a given value written somewhere inside text in their column B" I mean....

I want to be able to search an alphanumeric value "ABC12345" within a column that has a paragraph of text written in each row, I want to know which rows have this value written somewhere in them and have it return 2,4,33, as row 2 and 4 and 33 have this value in them inside column B on that row.

on column A i have the respective row numbers all the way down ie: ( 1,2,3,4, etc) (to pull up the row numbers)(unless you have a better way)

On Column B i have the paragraphs of text to search in (there is lots of text and spaces in the paragraphs.)

On column C i have the value to look for... ie: ABC12345 (different values in each row of column C) to be compared to all of column B

On column D would be the formula to Pull down and get the results.

and something tells me you might have a better way of getting the row numbers rather than having column A dedicated to numerating each row 1,2,3,etc

1
paragraphs of text
look for
it's found in row #

2
anything can be written here and might have ABC11112
DDD77777
4

3
anything can be written here and might have ABC12345
ABC55555
5,6

4
anything can be written here and might have DDD77777
BBB11111
Not Found

5
anything can be written here and might haveABC55555
ABC11112
2

6
anything can be written here and might haveABC55555
ABC12345
3

View 6 Replies View Related

VB String Written Cross Refers Tab2 On A Spreadsheet

Nov 14, 2006

I had the following VB string written for me recently, that cross refers Tab2 on a spreadsheet to tab1 with any duplicated fields in Tab1 being moved to Tab3.

Column B is the column I am comparing over the 2 sheets...


Sub DIVIDEND()

Dim Master As Worksheet 'Tab 1
Dim RefTab As Worksheet 'Tab 2
Dim NewTab As Worksheet 'Tab 3
Dim Cell As Range
Dim iRow As Long
Dim jRow As Long
Dim ff As String

The addition I would like to add is after this first action has taken place, I need all all of the data that is in Tab2 and not Tab1 moved to Tab 3.

To summarise, I need any info that appears in Tabs1 & 2 moved to Tab 3 and any remaining lines that are on Tab 2 and NOT Tab1 moved from Tab2 to Tab3.

View 9 Replies View Related

Multiple IF Statements Check If Words Are Written In Certian Boxes

Aug 15, 2007

I am trying to write a formula that will do multiple IF statements checking to see if words are written in certian boxes and if so to do certian things and I need help, never done multiple IF statements in one formula before and can't find anything in the books I have or on the web.

= IF(D30 = "Oceanic",
IF(D35 = "Yes",
Both True
(((D32 * 4) + (D33 * 5) + (D34 * 9) + D31) * 0.15) + ((D32 * 4) + (D33 * 5) + (D34 * 9) + (D31 + 4500)),

Oceanic = "Oceanic", D35 = "No"
(((D32 * 4) + (D33 * 5) + (D34 * 9) + D31) * 0.15) + ((D32 * 4) + (D33 * 5) + (D34 * 9) + D31)

Oceanic = False, D35 = "Yes"
((d32 * 4) + (d33 * 5) + (d34 * 9) + (4500 + d31))

Both False
((d32 * 4) + (d33 * 5) + (d34 * 9))

=======================================================
I got:

= IF(D30 = "Oceanic", IF(D35 = "Yes", (((D32 * 4) + (D33 * 5) + (D34 * 9) + D31) * 0.15) + ((D32 * 4) + (D33 * 5) + (D34 * 9) + (D31 + 4500)), (((D32 * 4) + (D33 * 5) + (D34 * 9) + D31) * 0.15) + ((D32 * 4) + (D33 * 5) + (D34 * 9) + D31), ((d32 * 4) + (d33 * 5) + (d34 * 9) + (4500 + d31)), ((d32 * 4) + (d33 * 5) + (d34 * 9))

View 14 Replies View Related

Lookup With Criteria: Amount To Be Written In Sheet 1 From Taking Value From Sheet2 Based On The Code

May 11, 2009

1 . Now i need amount to be written in sheet 1 from taking value from sheet2 based on the code. The code & description column has the same value. but it will be placed in different order

Ex:

the amount should get filled up only for the status yes & no . others it should return null.

View 3 Replies View Related

Avoid #N/A In Vlookup

Feb 22, 2008

I know how to avoid getting the #N/A in a vlookup, but I have found situations where I have data matches and still get #N/A. For instance, I am looking up an item number (which contains numbers and letters) on one tab to find corresponding info on another tab. I've verified that the item number is indeed in the lookup range but I still get the #N/A. If I copy and paste the item number over the same item number in the lookup range then it works. However, I don't want to do this for each item number.

View 9 Replies View Related

Formula To Avoid #DIV/0!

Jun 12, 2009

=AVERAGE(IF(A:A=B6,IF(C:C>0,C:C)))

"A" is a duplicated rep field and "B" is unique rep IDs and "C" is a sum of product types purchased. I'm averaging the number of product types sold by each rep.

I need to modify it to remove the #DIV/0! error it generates. It calculates just fine by row (with the error on some rows), but when I use that calculated field as an element in another formula, the #DIV/0! blows up the second formula. I tried a second IF function tied to the sales field "D" but I don't think I nested it right.

View 9 Replies View Related

Avoid N/A With Plotting A Chart

Mar 27, 2014

I have a set of data containing "N/A" in some cells, and when I try to plot the chart using stacked lines chart type, the chart is completely off. if the "N/A" are causing this problem and how to fix this.

View 5 Replies View Related

How To Avoid Duplicated Results

Jul 22, 2014

Transferring Multiple Data from separate worksheet without duplicating"

As you can see i am looking for information in column d (Letter) and getting the result to show from column B (number) using the formula in Column G (result 1).

The problem i am having is that if i try to copy or drag the formula down into Result 2 i just get the same answer.

Is there any way for the next cell down to ignore the previous result?

View 14 Replies View Related

Avoid Long IF Statement.

Jan 5, 2010

I've attached an example of what I am trying to achieve and an explanation as well. I'm trying to avoid the use of a macro as I don't understand them and I will need to modify the sheet later on which could cause problems. However, if a macro is the only solution, then I will have to make due with one.

View 5 Replies View Related

Avoid Save File

Apr 30, 2009

I would like to avoid user to SAVE or SAVE AS file. In my macro I used code to disable SAVE and SAVE AS from FILE menu. But how to avoid to save file if user use CTRL+S command to save file?

View 7 Replies View Related

VBA For To Avoid Pressing F2 Then Enter

Nov 10, 2009

I have a formulas in Sheet1 linked to other sheets, but I have only formula and there are no sheets ( eg: in sheet 1 A1 =Data!A1) where as there is no sheet called Data. I have a macro to import this sheets from a different file.

The problem I am facing is, after importing the sheets, the formula still shows #REF but if I do double click the cell or press F2 and then "Enter" the value appears. I have about 1200 formulas in this sheets.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved