Hi, Any help would be greatly appreciated..
I have a cell with a drop down box that allows me to select only two items
S&P500 or RUSSELL 2000
each of these two options has its own formula to calculates the
profitability of each.
S&P500 has the formula =((J12-F12)*50*B12)+(B12*1000)+(-2.5*B12)
RUSSELL 2000 has the formula =((J20-F20)*100*B20)+(B20*1000)+(-2.5*B20)
I'd like to have a single cell which incorporates both formulas and works
out the profitabilty depending on the selected item from the drop down (ie.
S&P500 or RUSSELL 2000), and ignores the formula for the item not selected
from the drop down box...
Looking forward to some help!
Thanks All :) Cheers.

=((J12-F12)*50*B12)+(B12*1000)+(-2.5*B12)*(dv_cell="S&P500")+
((J20-F20)*100*B20)+(B20*1000)+(-2.5*B20)*(dv_cell="RUSSELL 2000")


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"inmelbvic" wrote in message
...
Hi, Any help would be greatly appreciated..
I have a cell with a drop down box that allows me to select only two items
S&P500 or RUSSELL 2000
each of these two options has its own formula to calculates the
profitability of each.
S&P500 has the formula =((J12-F12)*50*B12)+(B12*1000)+(-2.5*B12)
RUSSELL 2000 has the formula =((J20-F20)*100*B20)+(B20*1000)+(-2.5*B20)
I'd like to have a single cell which incorporates both formulas and works
out the profitabilty depending on the selected item from the drop down
(ie.
S&P500 or RUSSELL 2000), and ignores the formula for the item not selected
from the drop down box...
Looking forward to some help!
Thanks All :) Cheers.

Another idea for the 1st part. Work it out for the 2nd
=((J12-F12)*50-2.5+1000)*B12

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Bob Phillips" wrote in message
...
=((J12-F12)*50*B12)+(B12*1000)+(-2.5*B12)*(dv_cell="S&P500")+
((J20-F20)*100*B20)+(B20*1000)+(-2.5*B20)*(dv_cell="RUSSELL 2000")


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"inmelbvic" wrote in message
...
Hi, Any help would be greatly appreciated..
I have a cell with a drop down box that allows me to select only two
items
S&P500 or RUSSELL 2000
each of these two options has its own formula to calculates the
profitability of each.
S&P500 has the formula =((J12-F12)*50*B12)+(B12*1000)+(-2.5*B12)
RUSSELL 2000 has the formula =((J20-F20)*100*B20)+(B20*1000)+(-2.5*B20)
I'd like to have a single cell which incorporates both formulas and works
out the profitabilty depending on the selected item from the drop down
(ie.
S&P500 or RUSSELL 2000), and ignores the formula for the item not
selected
from the drop down box...
Looking forward to some help!
Thanks All :) Cheers.

Thanks Bob...
unfortunately the formula only returns the reply #NAME? could you suggest
anything to fix??

Cheers


"Bob Phillips" wrote:

=((J12-F12)*50*B12)+(B12*1000)+(-2.5*B12)*(dv_cell="S&P500")+
((J20-F20)*100*B20)+(B20*1000)+(-2.5*B20)*(dv_cell="RUSSELL 2000")


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"inmelbvic" wrote in message
...
Hi, Any help would be greatly appreciated..
I have a cell with a drop down box that allows me to select only two items
S&P500 or RUSSELL 2000
each of these two options has its own formula to calculates the
profitability of each.
S&P500 has the formula =((J12-F12)*50*B12)+(B12*1000)+(-2.5*B12)
RUSSELL 2000 has the formula =((J20-F20)*100*B20)+(B20*1000)+(-2.5*B20)
I'd like to have a single cell which incorporates both formulas and works
out the profitabilty depending on the selected item from the drop down
(ie.
S&P500 or RUSSELL 2000), and ignores the formula for the item not selected
from the drop down box...
Looking forward to some help!
Thanks All :) Cheers.

You'll need to replace dv_cell in the formula by the reference of your input
cell (or name that cell dv_cell).
--
David Biddulph

"inmelbvic" wrote in message
...
Thanks Bob...
unfortunately the formula only returns the reply #NAME? could you suggest
anything to fix??

"Bob Phillips" wrote:

=((J12-F12)*50*B12)+(B12*1000)+(-2.5*B12)*(dv_cell="S&P500")+
((J20-F20)*100*B20)+(B20*1000)+(-2.5*B20)*(dv_cell="RUSSELL 2000")

"inmelbvic" wrote in message
...
Hi, Any help would be greatly appreciated..
I have a cell with a drop down box that allows me to select only two
items
S&P500 or RUSSELL 2000
each of these two options has its own formula to calculates the
profitability of each.
S&P500 has the formula =((J12-F12)*50*B12)+(B12*1000)+(-2.5*B12)
RUSSELL 2000 has the formula =((J20-F20)*100*B20)+(B20*1000)+(-2.5*B20)
I'd like to have a single cell which incorporates both formulas and
works
out the profitabilty depending on the selected item from the drop down
(ie.
S&P500 or RUSSELL 2000), and ignores the formula for the item not
selected
from the drop down box...
Looking forward to some help!
Thanks All :) Cheers.