Google Answers Logo
View Question
 
Q: Excel Formulas ( Answered 5 out of 5 stars,   4 Comments )
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.
Answer  
Subject: Re: Excel Formulas
Answered By: tar_heel_v-ga on 07 Oct 2004 14:17 PDT
Rated:5 out of 5 stars
 
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:5 out of 5 stars and gave an additional tip of: $3.00
Great!

Comments  
Subject: Re: Excel Formulas
From: nrduncan-ga 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: nrduncan-ga 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: nrduncan-ga 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: cashindahat-ga on 08 Oct 2004 14:22 PDT
 
nrduncan-ga: Thanks for the comment!  It worked!  Many thanks.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy