

Subject:
Excel Formulas
Category: Computers > Algorithms Asked by: cashindahatga 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!  
 
 


Subject:
Re: Excel Formulas
Answered By: tar_heel_vga 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 enduser 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 
cashindahatga
rated this answer:
and gave an additional tip of:
$3.00
Great! 

Subject:
Re: Excel Formulas
From: nrduncanga on 07 Oct 2004 13:19 PDT 
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 :) 
Subject:
Re: Excel Formulas
From: nrduncanga on 07 Oct 2004 13:28 PDT 
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? 
Subject:
Re: Excel Formulas
From: nrduncanga on 07 Oct 2004 14:23 PDT 
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). 
Subject:
Re: Excel Formulas
From: cashindahatga on 08 Oct 2004 14:22 PDT 
nrduncanga: Thanks for the comment! It worked! Many thanks. 
If you feel that you have found inappropriate content, please let us know by emailing us at answerssupport@google.com with the question ID listed above. Thank you. 
Search Google Answers for 
Google Home  Answers FAQ  Terms of Service  Privacy Policy 