Formula Referencing Cell Contents Not Value

deannleona

New Member
Joined
Nov 12, 2008
Messages
5
Goal:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
To determine the value of a potential client based on given variables, the farther along in the sales phase, the greater percentage of the value remains. <o:p></o:p>
Variables:<o:p></o:p>
Sales Phase Plan Old, keep 5% of Prospect Value<o:p></o:p>
Sales Phase Overview, keep 25% <o:p></o:p>
Sales Phase Workshop, keep 50%<o:p></o:p>
Sales Phase Plan New, keep 75%<o:p></o:p>
If Yes to a referral, 5% additional<o:p></o:p>
My sample data:<o:p></o:p>
<TABLE class=MsoNormalTable style="MARGIN: auto auto auto 4.65pt; WIDTH: 423pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0in 5.4pt 0in 5.4pt" cellSpacing=0 cellPadding=0 width=564 border=0><TBODY><TR style="HEIGHT: 11.65pt; mso-yfti-irow: 0"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 97pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.65pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .5pt" vAlign=top width=129>
A<o:p></o:p>




</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #e1e0d2; WIDTH: 78pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.65pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=top width=104>
B<o:p></o:p>




</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #e1e0d2; WIDTH: 87pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.65pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom width=116>
C<o:p></o:p>




</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #e1e0d2; WIDTH: 1.25in; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.65pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=top width=120>
D<o:p></o:p>




</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #e1e0d2; WIDTH: 71pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 11.65pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=top width=95>
E<o:p></o:p>




</TD></TR><TR style="HEIGHT: 30pt; mso-height-source: userset; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e1e0d2; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 97pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=top width=129>
Prospect Name<o:p></o:p>




</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e1e0d2; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #e1e0d2; WIDTH: 78pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=top width=104>
Potential Value<o:p></o:p>




</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e1e0d2; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #e1e0d2; WIDTH: 87pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom width=116 x:fmla='="Phase"'>
Phase<o:p></o:p>




</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e1e0d2; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #e1e0d2; WIDTH: 1.25in; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=top width=120>
Referral Yes or No<o:p></o:p>




</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e1e0d2; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #e1e0d2; WIDTH: 71pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=top width=95>
Current Value<o:p></o:p>




</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e1e0d2; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 97pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=top width=129>
Client A<o:p></o:p>



</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e1e0d2; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #e1e0d2; WIDTH: 78pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=top width=104 x:num="5000">
5,000<o:p></o:p>



</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e1e0d2; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #e1e0d2; WIDTH: 87pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=top width=116>
Plan New<o:p></o:p>




</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e1e0d2; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #e1e0d2; WIDTH: 1.25in; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=top width=120>
Yes<o:p></o:p>



</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e1e0d2; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #e1e0d2; WIDTH: 71pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=top width=95 x:num="3937.5" x:fmla="=SUM(3750+187.5)">
3,937.50<o:p></o:p>



</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset; mso-yfti-irow: 3"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e1e0d2; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 97pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=top width=129>
Client B<o:p></o:p>



</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e1e0d2; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #e1e0d2; WIDTH: 78pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=top width=104 x:num="2000">
2,000<o:p></o:p>



</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e1e0d2; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #e1e0d2; WIDTH: 87pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=top width=116>
Workshop<o:p></o:p>




</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e1e0d2; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #e1e0d2; WIDTH: 1.25in; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=top width=120>
No<o:p></o:p>



</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e1e0d2; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #e1e0d2; WIDTH: 71pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=top width=95 x:num="1000">
1,000.00<o:p></o:p>



</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset; mso-yfti-irow: 4"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e1e0d2; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 97pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=top width=129>
Client C<o:p></o:p>



</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e1e0d2; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #e1e0d2; WIDTH: 78pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=top width=104 x:num="100000">
100,000<o:p></o:p>



</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e1e0d2; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #e1e0d2; WIDTH: 87pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=top width=116>
Overview<o:p></o:p>




</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e1e0d2; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #e1e0d2; WIDTH: 1.25in; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=top width=120>
Yes<o:p></o:p>



</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e1e0d2; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #e1e0d2; WIDTH: 71pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=top width=95 x:num="26250" x:fmla="=SUM(25000+1250)">
26,250.00<o:p></o:p>



</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset; mso-yfti-irow: 5; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e1e0d2; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 97pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt" vAlign=top width=129>
Client D<o:p></o:p>



</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e1e0d2; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #e1e0d2; WIDTH: 78pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=top width=104 x:num="50000">
50,000<o:p></o:p>



</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e1e0d2; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #e1e0d2; WIDTH: 87pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=top width=116>
Plan Old<o:p></o:p>




</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e1e0d2; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #e1e0d2; WIDTH: 1.25in; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=top width=120>
No<o:p></o:p>



</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #e1e0d2; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #e1e0d2; WIDTH: 71pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=top width=95 x:num="2500">
2,500.00<o:p></o:p>



</TD></TR></TBODY></TABLE>​
My formula so far:<o:p></o:p>
IF(C2="Plan Old",B2*5%,IF(C2="Overview",B2*25%,IF(C2="Workshop",B2*50%,IF(C2="Plan New",B2*75%,""))))<o:p></o:p>
<o:p></o:p>
Since I am still struggling with this formula, I haven't yet added in the "referral 5%" to my formula but I did figure it in when I put the answers in the current values. <o:p></o:p>
<o:p></o:p>
Do I have to tell the formula to consider "content" not values?<o:p></o:p>
I am in Excel 2002. I am not getting an error message; the formula just stays in the cell as text.
Thanks in advance for your guidance.

PS. Is it okay to tell you that sometimes Excel makes me cry?
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
I am in Excel 2002. I am not getting an error message; the formula just stays in the cell as text.
Select the cell with the formula and right click, select format cell, on the number tab change to general, click ok. Then press F2 and <enter>
 

RZlot

New Member
Joined
Apr 16, 2009
Messages
4
the formula you have does the first part correctly. To make it simple put that formula in column f.
Then type

If(d2="Yes", F2*1.05,F2) in the Current Value cell.

Not elegant but it should work.
 

deannleona

New Member
Joined
Nov 12, 2008
Messages
5
HA! The only thing I forgot was to add the = at the beginning of the formula!

Thank you for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,102
Messages
5,412,438
Members
403,427
Latest member
mese0115

This Week's Hot Topics

Top