Calculation using the last value in a cell

bj6264

New Member
Joined
May 23, 2010
Messages
49
Hello, can someone help please. I have a list of prices but some cells contain multiple values where the value has changed. I need a formula that will use the last value in a cell. Example below. As you can see the result column has now value as its using $3.41$3.117 in the calculation (I need it to use $3.117)

PriceUpliftCalculationResult
<S>$3.41</S>

$3.117
0.8=A2*B2#VALUE!
<S>$5.80
</S>

<S>$5.63</S>

$4.634
1.3=A3*B3#VALUE!
$1.7112.3=A4*B43.9353

<COLGROUP><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" span=4 width=81><TBODY>
</TBODY>

</SPAN>
<STRIKE></STRIKE></SPAN>
</SPAN>

</SPAN></SPAN></SPAN>
</SPAN></SPAN></SPAN></SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=4></COLGROUP>
</SPAN></SPAN>
</SPAN>
</SPAN>
</SPAN>
</SPAN>
</SPAN>
</SPAN></SPAN></SPAN></SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=4></COLGROUP>
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,562
Office Version
2010
Platform
Windows
Hello, can someone help please. I have a list of prices but some cells contain multiple values where the value has changed. I need a formula that will use the last value in a cell. Example below. As you can see the result column has now value as its using $3.41$3.117 in the calculation (I need it to use $3.117)

Price
Uplift
Calculation
Result
<S>$3.41</S>

$3.117
0.8
=A2*B2
#VALUE!
<S>$5.80
</S>

<S>$5.63</S>

$4.634
1.3
=A3*B3
#VALUE!
$1.711
2.3
=A4*B4
3.9353

<TBODY>
</TBODY>
I think this formula in C2 (copied down) should do what you want...

=TRIM(LEFT(RIGHT(SUBSTITUTE(CHAR(10)&A2,CHAR(10),REPT(" ",99)),99),99))*B2
 

Forum statistics

Threads
1,084,945
Messages
5,380,731
Members
401,696
Latest member
MDAUD

Some videos you may like

This Week's Hot Topics

Top