CSE Formula Required

[jim may]

I have below range of Text Values - I wish to have a formula in Cell B85 as follows emtered with CSE to produce({}):
to get result of: 190.51

[TABLE="width: 115"]
<tbody>[TR]
[TD="class: xl118, width: 115"]={SUM(LEFT(B72:B84,FIND(" ",B72:B84,1)-1)+0)}

But I'm Getting #VALUE !

How do I need to Adjust above existing Formula?[/TD]
[/TR]
</tbody>[/TABLE]

Excel 2010

	B
30.51 for Wk
40 for Wk
40 for Wk
40 for Wk
40 for Wk

<tbody>
[TD="align: center"]72[/TD]

[TD="align: center"]73[/TD]

[TD="align: center"]74[/TD]

[TD="align: center"]75[/TD]

[TD="align: center"]76[/TD]

[TD="align: center"]77[/TD]
[TD="align: right"][/TD]

[TD="align: center"]78[/TD]
[TD="align: right"][/TD]

[TD="align: center"]79[/TD]
[TD="align: right"][/TD]

[TD="align: center"]80[/TD]
[TD="align: right"][/TD]

[TD="align: center"]81[/TD]
[TD="align: right"][/TD]

[TD="align: center"]82[/TD]
[TD="align: right"][/TD]

[TD="align: center"]83[/TD]
[TD="align: right"][/TD]

[TD="align: center"]84[/TD]
[TD="align: right"][/TD]

</tbody>

Quick-Cash_Proj​

 

[Fluff]

Remove the {} from your formula, they should not be entered manually.
The whilst in the formula bar press Ctrl Shift Enter & the {} should then appear

 

[Logit]

When I entered the following into the empty cells, your formula using CSE delivers the value you were looking for :



[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

70​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

71​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

72​

[/td][td][/td][td]30.51 for Wk[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

73​

[/td][td][/td][td]40 for Wk[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

74​

[/td][td][/td][td]40 for Wk[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

75​

[/td][td][/td][td]40 for Wk[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

76​

[/td][td][/td][td]40 for Wk[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

77​

[/td][td][/td][td]0 for Wk[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

78​

[/td][td][/td][td]0 for Wk[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

79​

[/td][td][/td][td]0 for Wk[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

80​

[/td][td][/td][td]0 for Wk[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

81​

[/td][td][/td][td]0 for Wk[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

82​

[/td][td][/td][td]0 for Wk[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

83​

[/td][td][/td][td]0 for Wk[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

84​

[/td][td][/td][td]0 for Wk[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

85​

[/td][td][/td][td]

190.51​

[/td][/tr]
[/table]

 

[jim may]

I understand what you are saying -- in my formula the {}'s were NOT entered manually.

It seems the problem with my formula is the Blank Cells in the Range B77:B84.
When I alter the Range to ONLY B72:B76 it Works. Still prefer to use full range B72:B84.

What must I do?

Thanks,
Jim

 

[Fluff]

I assumed that you had entered the braces manually as the first one is in the wrong place.
Try

Excel 2013/2016
	B
1	30.51 for Wk
2	40 for Wk
3	40 for Wk
4	40 for Wk
5	40 for Wk
6
7
8
9
10
11
12
13
14
15
16
17
18
19	190.51
Master

Cell Formulas
Range	Formula
B19	{=SUM(IF(B1:B18<>"",LEFT(B1:B18,FIND(" ",B1:B18,1)-1)+0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Tetra201]

@ jim may:

Here is how to tweak your formula:

=SUM(LEFT(0&B72:B84,FIND(" ",0&B72:B84&" ",1)-1)+0)

Or even shorter:

=SUM(LEFT(0&B72:B84,FIND(" ",B72:B84&" ",1))+0)

 

[jim may]

Fluff - thanks for the additionsl IF(B1:B18<>"",
Works a treat, as they say...

Jim

 

[jim may]

Nice Alternative... Tetra201

Tks,

Jim

 

[Fluff]

You're welcome & thanks for the feedback.