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)))


== These are great ( google sheets ) ==
== 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.
 
   
   
=INDEX(D3:D8,MATCH("Product 4",A3:A8,1),1)


Find the index of the A coloumn that matches "Product 4", Give that to "INDEX" , get the cell inthe 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)


Sum all the entries from B where the entries in A match A2.
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:

=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"