2nd Que: IF/AND from drop-down lists?

[jacob farino]

Second issue, I'm hoping this will be the easier of the two:

I have a list of income sources in Column A. I have a total income listed in
Column B. I have a tax-deductible option (currently, drop down list 'YES' or
'NO') in Column C.

I have the income totalled at the bottom.

At the bottom of my sheet, I have a drop-down list for the end-user to
select which investor they are going to send this particular income
calculation sheet to. Depending on which investor they choose, I need to
mark-up the income total by that investor's particular mark-up, and only if
the income source is tax deductible.

For instance, I have three income sources listed. Each are $1000. Two are
tax deductible, one is not. I select an investor, investor X for example,
and I know that investor X allows me to gross up my non-taxable income by
25%. So, the formula would need to figure out that my sub-total income is
$3000. I can gross up $2000 of this ($2000 x 1.25 = $2500), so my TOTAL
income is actually $3500.
And if I select another investor, and their mark-up is 20%, then my new
income would be $3400.

Does this make sense? I'm not sure if drop-down lists are the way to go
here, or if you have an easier way to get what I need.


I'm using XP w/ excel 2002.

Thanks so much

Jacob

 

[martialtiger]

You have some different options available, but it all depends on ho
many investors and different values you need to determine which is bes
for you. If you're going to have a lot of investors listed, you migh
be better off creating a custom function to avoid the limitations o
the "IF" function (7 or less)

 

[jacob farino]

There will be about 12 investors. The different investor values should be
less than 7 though. Most investors use 125% or 120%. I don't believe there
are more than four different mark-up values used.

 

[martialtiger]

Then you might be able to accomplish it using IF and a few OR functions
If you need more assistance, please post how your data looks in th
spreadsheet

 

[jacob farino]

Okay, here are the details and the exact data:

A12 thru A17 = Income Source (text, user defined [examples would be Social
Security, Pension, etc.])

B12 thru B17 = Amount Received (will be currency in $dddd.cc format)

C12 thru C17 = Tax Deductible? (currenty have a drop-down list option, YES
or NO)

A18 = Subtotal (text)

B18 = Subtotal (calculated sum of above columns)

C18= [empty]

A20 = Choose an Investor (text)

B20 = Investor name (from drop down list)

C20 = Mark up amount (i.e., 125%, would like it to auto fill from the
investor chosen)

A22 = Total (text)

B22 = Total (calculated sum after any investor mark-up)

DATA:
Source can be any written text, not important to formula
Amount Received is the actual income received (i.e., $2002.50)
Tax Deductible will be either yes or no, user inputted
Investors and their corresponding mark up amount:
Acoustic 125%
Argent 120%
Countrywide 125%
First Franklin 127%
Indymac 125%
New Century 123%
Novastar 125%
Saxon 120%

GOAL: Like I mentioned earlier, for any line items (rows 12-17) I will mark
as either yes or no, if yes, then that amount received is to be multiplied
by the mark up, and the total line items will be summed in the total bar.
(perhaps the investor choice should be above the data so it is selected
first?)

I hope this clears things up, if not, please let me know, and thanks for
everyone's input.

Jacob