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.

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

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.