Polanskiman
Board Regular
- Joined
- Nov 29, 2011
- Messages
- 119
- 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: