Excel: Difference between revisions
From Federal Burro of Information
Jump to navigationJump to search
No edit summary |
No edit summary |
||
(3 intermediate revisions by the same user not shown) | |||
Line 13: | Line 13: | ||
=LEFT(J3,LEN(J3)-(LEN(J3)-FIND(" ",J3))) | =LEFT(J3,LEN(J3)-(LEN(J3)-FIND(" ",J3))) | ||
== | == google sheets == | ||
=== Find match index === | |||
=INDEX(D3:D8,MATCH("Product 4",A3:A8,1),1) | |||
Find the index of the A column that matches "Product 4", Give that to "INDEX" , get the cell in the range "D3:D8", at index pos of the previous match. | |||
=== Sum if match === | |||
Sum all the entries from B where the entries in A match A2. | |||
=SUMIF('RAW DATA'!A2:A, "A2:A = A2", 'RAW DATA'!B2:B) | =SUMIF('RAW DATA'!A2:A, "A2:A = A2", 'RAW DATA'!B2:B) | ||
this also works: | |||
=sum(filter(B2:B9, regexmatch(A2:A9, "STRING"))) | |||
Where STRING is the string in the column that should make it be included in the sum. | |||
=== Conditional formatting on more than one value === | |||
This is an example of a "custom format". | |||
The IF() is implied in the custom format formula: | |||
So: | |||
for =IF( A1 = "Paid" ) | |||
Becomes simply: | |||
A1 = "Paid" | |||
But what about "if this and that"? | |||
AND ( A1 = "Paid" , B1 > 0 ) | |||
== These are great ( google sheets ) == | |||
== regexp == | |||
=REGEXEXTRACT(D11,"^[0-9]+") | |||
== format bytes == | |||
<pre> | |||
[<1000000]0.00," KB";[<1000000000]0.00,," MB";0.00,,," GB" | |||
</pre> |
Latest revision as of 04:45, 28 February 2024
if match substr
my cells have unit in them... some MB , some KB, I want the mall to be a single unit so I can remove the uni and do math on it.
=IF(ISNUMBER(SEARCH("* MB",A1)),"dog","")
resources:
- http://www.techrepublic.com/article/using-excels-find-and-mid-to-extract-a-substring-when-you-dont-know-the-start-point/1033368
- http://www.techrepublic.com/article/save-time-by-using-excels-left-right-and-mid-string-functions/1033367
=LEFT(J3,LEN(J3)-(LEN(J3)-FIND(" ",J3)))
google sheets
Find match index
=INDEX(D3:D8,MATCH("Product 4",A3:A8,1),1)
Find the index of the A column that matches "Product 4", Give that to "INDEX" , get the cell in the range "D3:D8", at index pos of the previous match.
Sum if match
Sum all the entries from B where the entries in A match A2.
=SUMIF('RAW DATA'!A2:A, "A2:A = A2", 'RAW DATA'!B2:B)
this also works:
=sum(filter(B2:B9, regexmatch(A2:A9, "STRING")))
Where STRING is the string in the column that should make it be included in the sum.
Conditional formatting on more than one value
This is an example of a "custom format".
The IF() is implied in the custom format formula:
So:
for =IF( A1 = "Paid" )
Becomes simply:
A1 = "Paid"
But what about "if this and that"?
AND ( A1 = "Paid" , B1 > 0 )
These are great ( google sheets )
regexp
=REGEXEXTRACT(D11,"^[0-9]+")
format bytes
[<1000000]0.00," KB";[<1000000000]0.00,," MB";0.00,,," GB"