Excel

From Omnia
Revision as of 19:23, 23 August 2020 by Kenneth (talk | contribs) (→‎Count if Checked)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

Interest Rates

OeOvmHV.png

Value in Range

=IF(COUNTIF(range,value),"Yes","No")
=IF(COUNTIF(range,value)>1,"Yes","No")

ref: [1]

cell contains substring

=ISNUMBER(SEARCH("Some Text", A5))
=if(ISNUMBER(search(J4,K4)),"","BAD")


ref: [2]

Convert unix timestamp to date time string

A1 = unix time stamp
B1 = 25569 epoc shift (days since 1/1/1900 and 1/1/1970
       ="1/1/1970"-"1/1/1900"+1
C1 = -6 hours away from GMT
= ( A1 / 1000 + B1 * 86400 + C1 * 3600 ) / 86400

Sample:

1537822800000 -> 09-24-18 15:00:00

Count if Checked

=SUMPRODUCT(($D$2:$D$15=TRUE)*$C$2:$C$15)
=SUMPRODUCT(($D$2:$D$15="X")*$C$2:$C$15)
Column D has "X"
Column C has $ values

https://www.extendoffice.com/documents/excel/2460-excel-sum-count-checkboxes.html

Split by delimiter

Split a single column that has a delimiter

"Smith, John"
=LEFT(A2, SEARCH(",",A2,1)-1)  # Smith
=RIGHT(A2, LEN(A2)-SEARCH(",",A2,1)-1)  # John


Ref [3]

keywords