Polanskiman
Board Regular
 Joined
 Nov 29, 2011
 Messages
 107
 Office Version

 365
 2016
 2011
 2010
 2007
 Platform

 Windows
 MacOS
 Mobile
Hello,
I have this set of data (See below).
For column A:
Sum seem to be working as long as both G15 AND G16 are not empty. Else it throws a #VALUE!. Obviously I can't have this happen since cells in the G13:G16 range can in some instance be empty.
For column B:
If either H15 or H16 (or both) values are absent then it throws a #VALUE! also. Same here. I can't have this happen.
I need that degree character to stay where it is else the calculation would be a breeze.
Can enyone help me fixing those formulas. I have been banging my head for quite some time now trying to make this work.
Thank you.
I have this set of data (See below).
For column A:
Sum seem to be working as long as both G15 AND G16 are not empty. Else it throws a #VALUE!. Obviously I can't have this happen since cells in the G13:G16 range can in some instance be empty.
For column B:
If either H15 or H16 (or both) values are absent then it throws a #VALUE! also. Same here. I can't have this happen.
I need that degree character to stay where it is else the calculation would be a breeze.
Can enyone help me fixing those formulas. I have been banging my head for quite some time now trying to make this work.
Thank you.
Book2  

E  F  G  H  I  
6  
7  
8  multipler  
9  2  
10  
11  
12  Column A  Column B  
13  1 °  
14  2 °  
15  3 °  1 °  
16  4 °  2 °  
17  Sub total white  3 °  2 °  
18  Total white + yellow  13 °  5 °  
19  
20  
Sheet1 
Cell Formulas  

Range  Formula  
G13,H15  G13  =1&" °" 
G14,H16  G14  =2&" °" 
G15  G15  =3&" °" 
G16  G16  =4&" °" 
G17  G17  =IF(SUM(IF(ISBLANK($G$13:$G$14),"",IFERROR(VALUE(LEFT($G$13:$G$14,SEARCH(" °",$G$13:$G$14)1)),$G$13:$G$14)))=0,"",SUM(IF(ISBLANK($G$13:$G$14),"",IFERROR(VALUE(LEFT($G$13:$G$14,SEARCH(" °",$G$13:$G$14)1)),$G$13:$G$14)))&" °") 
H17  H17  =IF(SUM(IF(ISBLANK($H$16),"",IFERROR(VALUE(LEFT($H$16,SEARCH(" °",$H$16)1)),$H$16)))=0,"",SUM(IF(ISBLANK($H$16),"",IFERROR(VALUE(LEFT($H$16,SEARCH(" °",$H$16)1)),$H$16)))&" °") 
G18  G18  =IF(AND(G15="",G16="",G17=""),"",IF(G17="",SUM(VALUE(LEFT($G$15,SEARCH(" °",$G$15)1)),VALUE(LEFT($G$16,SEARCH(" °",$G$16)1))),SUM(SUM(VALUE(LEFT($G$15,SEARCH(" °",$G$15)1)),VALUE(LEFT($G$16,SEARCH(" °",$G$16)1))),(VALUE(LEFT($G$17,SEARCH(" °",$G$17)1))*$F$9)))&" °") 
H18  H18  =IF(AND(H15="",H17=""),"",IF(H17="",VALUE(LEFT(H15,SEARCH(" °",H15)1)),SUM(VALUE(LEFT(H15,SEARCH(" °",H15)1)),VALUE(LEFT(H17,SEARCH(" °",H17)1))*F9))&" °") 
Last edited: