Help with IF statements

[JStiehl]

I need to create an IF statement that gets around the limitation of only 7
functions. I've tried the LOOKUP functions, but they don't seem to work for
my situation because a multiplication function must take place in the second
set of parenthesis. Here is what I need to do:

People using the form I'm creating will enter their income in one cell and
then choose one of the following from a drop down menu in another cell:
yearly, monthly, twice per month, per 2 weeks, per week. I need to convert
the income totals to the same format (yearly) before adding the numbers
together in a new cell for a total (income totals will be multiplied by 12,
24, 26 or 52 depending on their choice from the drop down menu). There is the
potential for 25 different incomes to be entered on this form (thus my
problem).

I am really having trouble figuring this out and need some step by step
help. Thank you so much!

 

[Sandy Mann]

If I understand you correctly then with the income in G10 and the frequency,
("Monthly" etc), in H10 then:

=G10*CHOOSE(MATCH(H10,{"Yearly","Monthly","Twice Per Month","Per Two
Weeks","Per Week"},0),1,12,24,26,52)

will multiply the income by the correct frequency.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[JStiehl]

Thank you for your help. This worked great, but unfortunately Excel only let
me enter this formula for 10 of the 25 income cells before I received the
error message "Formula too long". Any ideas how to get around this? Thanks
so much for your help.

 

[Sandy Mann]

I have no idea why it would give you that error, I have pasted the formula
from the post into 1,000 cells, filled it into 1,000 cells and dragged it
down 1,000 without any problem.

You can if you like send me a copy of your workbook, (sanitised of all
confidential information), assuming that it is not XL2007 because I have
only got XL97. Replace the part of my address below as it says by
substituting after the @ sign.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Jacqueline,

I replied to your email with an amended spreadsheet but it had just bounced
back to me. Can you please send me an alternative address to try?

Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk