View Question
 Question
 Subject: Excel Formulas Category: Computers > Algorithms Asked by: cashindahat-ga List Price: \$10.00 Posted: 07 Oct 2004 12:37 PDT Expires: 06 Nov 2004 11:37 PST Question ID: 411650
 ```Ex: Inputter can enter either a monthly expense for an item or an annual expense for the item. If she enters the monthly, the annual must be calculated (monthly times 12), conversely if the annual is entered, one monthly must be calculated (annual / 12). Need the output to display BOTH the monthly & annual figures for the inputter's one entry. Will tip well for a timely answer. Many thanks in advance!``` Request for Question Clarification by tar_heel_v-ga on 07 Oct 2004 12:49 PDT ```Is the data being entered into the same cell or is the option of two cells/columns, labeled monthly expense and annual expense and then convert from there for totals.``` Clarification of Question by cashindahat-ga on 07 Oct 2004 13:25 PDT ```The inputter enters the monthly expense in one column, or the annual expense in another column. Need output cells that display monthly in one column plus annually in the other.``` Clarification of Question by cashindahat-ga on 07 Oct 2004 13:55 PDT `nrduncan-ga: 4 columns.`
 Subject: Re: Excel Formulas Answered By: tar_heel_v-ga on 07 Oct 2004 14:17 PDT Rated:
 ```cashindahat... I have created a spreadsheet that I feel will meet your needs. The user enters their expense in column A and then choose A for annual or M for monthly in column B. If nothing is entered in column B, they are instructed to choose one or the other. When the letter is entered, the other columns then calculate appropriately. You may notice that column F is hidden. This should remain hidden as this is strictly where calculations take place and no need for the end-user to see. You can download the spreadsheet at http://www.freewebs.com/tarheelv/expenses.zip Please let me know if you need any additional clarification. -THV```
 cashindahat-ga rated this answer: and gave an additional tip of: \$3.00 `Great!`

 ```is there some way to tell if the input is monthly or annual? Options might include: 1) entering the value in a different cell 2) If the Value entered is less than some specified value then it's monthly, otherwise, annual 3) They mark one cell with an X if it's monthly, leave it alone for annual, then enter their value would any of those options work? As far as I know, they still haven't implemented the psychic module for Excel :)```
 ```So your sheet needs 2 or 4 columns? 2 columns would be, you enter your value in one, the other is calculated based on the value entered (i.e., you enter your value in annual, the monthly is calculated. You enter your value in monthly, the annual is automatically calculated). 4 columns would be, you have a monthly and annual input column, and a monthly and annual output column?```
 ```I did it by doing the following: My "A" column was my Monthly Input Column. My "B" column was my Yearly Input Column. My "C" column was my Monthly Output Column. My "D" column was my Yearly Output Column. In the C column, I entered the following formula (row 2): =IF(ISBLANK(A2),B2/12,A2) In the D column, I entered the following formula (row 2): =IF(ISBLANK(B2),A2*12,B2) If you copy these forumas down (using the little black box in the bottom right of the selected cell, just drag down), it should apply these values down the sheet (as far as you drag that is) The way The formulas work is the following: IF({Conditional Value},{True Exp.},{False Exp.}) Where {Conditional Value} is some expression that can be related as TRUE or FALSE. In this case I used The ISBLANK function (explained below). {True Exp} is the value to be displayed in the cell if the conditional value is evaluated to be TRUE {False Exp} is the value to be displayed in the cell if the conditional value is evaluated to be FALSE ISBLANK({Cell Ref.}) IsBlank returns TRUE or FALSE based on if the cell specified is blank (i.e. TRUE if no value has been entered, FALSE if a value has been entered). WHERE {Cell Ref} is the cell to test if it is blank or not. So, to break down my formula for the C column: =IF(ISBLANK(A2),B2/12,A2) ISBLANK(A2) returns based on if the A2 column has a value or not. If you remember from above, my A column is my monthly input column. So if the user enters a value in the A column, the the expression will return TRUE, if no value is entered in the A column, the expression will return FALSE. So...if A2 does not have a value entered, then the value displayed in the cell is B2/12, or, the annual value divided by 12 (which is the monthly value, which is what column C should display). If A2 does have a value entered, then A2 is used (the monthly input value, which is what column should display).```
 `nrduncan-ga: Thanks for the comment! It worked! Many thanks.`