Excel
External Links:
• Check IF a Value Exists in a Range
200807
An alternative to learn excel practical.
#
1. Rank
#
2. Pick text randomly from a list
#
3. Collect data leaving blanks:
#
4. Ignore characters when it counts sum of
values in a row that contains the characters
# 5. Let color of a cell in a range change when its value is typed
in a specific cell:
# 6. How can I achieve from
# 7. If D6 has a value smaller than 5, multiply it with 5. Otherwise
copy it.
# 8. If A6 is empty, put a dash. If it is greater than 20, copy it
but if smaller, make it 20.
# 9. Generate random text in a cell from a column (AP):
# 10. Blank Relation:
#
11. Add cells excluding some:
#
12. Paste column in reverse order:
#
13. If D2=EUROPE I want B2 to be
colored yellow.
#
14. Grade
#
15. If the value in A1 is blank, put
a dash, if greater than 20, copy it and if less, make 20.
#
16. Multiply cell A1 with 5 if the
number is smaller than 5, otherwise copy it.
# 17. Round and vlookup with percentage:
# 18. Delete the last two letters:
# 19. Count Ss in a grading range:
# 20. Round into a digit after decimal:
# 21. Data Validation:
# 22. Sum ignoring error:
# 23. vlookup picture
# 24. All Th Abs times:
# 25. Alternative way for section wise Roll No.
# 26. Change all caps into first letter only cap in a sentence:
# 27. Change all caps into first letter only cap in each word:
# 28. Copy only non blank cells (horizontal):
# 29. Copy only non blank cells (vertical):
# 30. Count if corresponding cells not empty
# 31. Count if greater than another cell
# 32. Decimal alignment:
# 33. Feedback in Remark:
# 34. Final Absent Times
# 35. Grade point average:
# 36. Grade points:
# 37. Grades
# 38. Grades for 11 n 12:
# 39. Highlight exact match
# 40. Hyperlink with a condition:
# 41. Insert file name in a cell
# 42. Maximum of the cells only with roll no. (array)
# 43. RanK (array)
# 44. Rank (st, th, etc.)
# 45. Score not more than a limit plus some score:
# 46. Vlookup from a table:
# 47. sum same name scores
#
48. Don't allow duplicate values (Data
Validation)
#
49. Count cells equal to or less than
another cell
#
50. Copy only unique values from a list
#
51. Copy cells ignoring error
# 53. Move cursor
# 54. Second largest/ Maximum, Second smallest/ Minimum value from a
list
#
55. Highlight only "Ab" for
absent : conditional formatting
#
56. Allow only a value less than or
equal to a cell and "Ab": Data validation
#
57. Vlookup and sum from a column
#58.
Countifs with double criteria
#59.
Dynamically extract a list of unique values from a
column range with formula
#60.
Count cells that contain errors
#61.
Combine cell value/ reference in
formula
#62. Convert column letter into number
#63.
Cell content as Vlookup array table
#64.
Count ignoring blanks with formula
#65. Fill A to Z
#66. Translate
#67. Retrieving the Last Value in a Column
#
1. Rank
{=SUM(--ISNUMBER(MATCH(ROW(INDIRECT("1:"&RANK(AB8,$AB$8:$AB$78))),RANK($AB$8:$AB$78,$AB$8:$AB$78),0)))}
=RANK(C19,$C$19:$C$22)
#
2. Pick text randomly from a list
=INDEX($L20:$L22,RANDBETWEEN(1,COUNTA($L20:$L22)),1)
#
3. Collect data leaving blanks:
{=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(B:B,SMALL(IF($B$2:$B$51<>"",ROW($B$2:$B$51)),ROWS($C$2:C2)))))}
Here,
B= Scattered data, C=New Colum
#
4. Ignore characters when it counts sum of values in a row that contains the
characters
=sum(A1:A5) [for regular cells]
=sum(A1,D1,G1) [while picking up
different cells, no '+')
#
5. Let color of a cell in a range change when its value is typed in a specific
cell:
Select range>conditional
format> =A1=$C$1
(C1 is a fixed cell where type a
value and the cell containing that value in the previously selected range
changes.)
=Q2=$B$2
#
6. How can I achieve from
1 Apple Grapes Orange Banana
2
3
to this
1 Apple
2 Grapes
3 Orange
Type in B5 and drag down:
=INDEX(TRIM(MID(REPT("",LEN(B$1))&SUBSTITUTE(B$1,"",REPT("",LEN(B$1))),COLUMN($A:$D)*LEN(B$1)+1,LEN(B$1))),A5)
Type the numbers manually in A5
& down.
#
7. If D6 has a value smaller than 5, multiply it with 5. Otherwise copy it.
IF(D6<5,D6*5,D6)
#
8. If A6 is empty, put a dash. If it is greater than 20, copy it but if
smaller, make it 20.
=IF(A6="","-",IF(A6>20,A6,20))
#
9. Generate random text in a cell from a column (AP):
=INDEX($AP:$AP,RANDBETWEEN(1,COUNTA($AP:$AP)),1)
#
10. Blank Relation:
if T9 is blank, clear the current
cell, otherwise copy cell AK4:
=IF(T9=ISBLANK(TRUE),"",AK4)
#
11. add cells excluding some:
=SUM(A1:CV1)-SUM(E1,M1,Y1,AB1,BU1)
#
12. Paste column in reverse order:
B1: =INDEX(A:A,COUNTA(A:A)-(ROWS(B$1:B1)-1))
copied down
Where A holds values... assumes
no blanks... can be adjusted if required. (Once complete you can copy B and
paste over B as Values only to dispose of formulae if required)
> An alternative
=OFFSET($A$1,COUNTA(A:A)-ROW(),0)
will work if your data always starts in $A$1 if it starts somewhere else modify
as necessary. If there is other information below in column A you need to
specify the range in the COUNTA formula
> Another simplier solution is
to insert numbers down in an adjacent column to the last entry, then select
both columns and reverse sort by the numbered column
> Try this one, even if you
heve blank cells it will work,
B1:
=INDEX($A$1:$A$10,ROWS(A1:A10)-ROWS($B$1:B1)+1)
#
13. If D2=EUROPE I want B2 to be colored yellow.
Select B2 and apply this
conditional format formula.
=D2="Europe"
If
the cells in column M are empty, highlight cells in their respective rows:
Click
N1 and apply this conditional format formula.
=M1=""
In
"Applies To", type the range, e.g.: N1:N9
#
14. Grade
CELL A1 - 7 AND GREATER THAN 7 =
A
IN BETWEEN 4 TO 6 = B
3 AND LESS THAN 3 = C
=IF(A1<4,"C",IF(A1<7,"B","A"))
=LOOKUP(A1,{0,4,7},{"C","B","A"})
#
15. If the value in A1 is blank, put a dash, if greater than 20, copy it and if
less, make 20.
=IF(A1="","-",IF(A1>20,A1,20))
#
16. Multiply cell A1 with 5 if the
number is smaller than 5, otherwise copy it.
=IF(A1<5,A1*5,A1)
#
17. Round and vlookup with percentage:
=ROUND(VLOOKUP($Y$5,Ledger!$A$9:$CA$107,75),1)&"%"
=ROUND(VLOOKUP($Y$5,Ledger!$A$9:$CA$107,74),1)
#
18. Delete the last two letters:
=LEFT(A1,LEN(A1)-2)
#
19. Count Ss in a grading range:
=COUNTIFS('Terminal
Ledger'!$P$7:$P$115,"<="&D13,'Terminal Ledger'!$P$7:$P$115,">"&D14)
#
20. Round into a digit after decimal:
=ROUND(SUM(O14:O22)/9,1)
=ROUND(VLOOKUP($Y$4,Ledger!$A$9:$CA$107,74),1)
#
21. Data Validation:
Only even numbers not more than a cell:
=and(a4>0,a4<=b4,mod(a4,2)=0)
=AND(AI4>0,AI4<=AI6,MOD(AI4,2)=0)
=MOD(AI4,2)=0
=ISEVEN(AI4)
(needs analysis toolpack (?))
Only even numbers not more than a
cell:
=ISODD(AI4)
(needs analysis toolpack (?))
Either Ab, 0 or a value less than or
equal to a cell allowed:
=OR(AR8="Ab",AND(AR8>=0,AR8<=$AR$7))
#
22. Sum ignoring Value N/A error: =SUMIF(J12:J20,"<>#N/A")
Sum ignoring Error in Value: =SUMIF(K12:K20,"<>#value!")
#
23. vlookup picture
>Create a table with two
columns, left with names and right with images completely within cells.
Row/col. A B
2 Name Images
3 Cow (cow)
4 Dog (dog)
5 Cat (cat)
>Create a next table exactly
the same.
Row/col. E F
2 Name Images
3
>Copy the cell (not the
picture) of (B3) and paste it in F3 as a link picture.
>[Optional: Click E3 > Data
> Data validation > List > Select A3:A5.]
>Formulas > Name Manager
> New > Give a new name (e.g. animal) > Refers to > clear the
content and type: =index(> select B3:B5> , > match( >
select E3> , > select A3:A5> ,0)) >
It may look like: =index(Sheet1!$B$3:$B$5,match(Sheet1!$E$3,Sheet1!$A$3:$A$5,0))
>Select the PICTURE in F3 and
rename it in the formula bar from something like =$AO$24 to the name you kept
in the previous step with = and name e.g.: =animal
> Get the image of your desire
by selecting the name in the dropdown list in the second table.
> Now, you are free to replace
the name or the pictures or to insert a new item in between, cut, copy and
paste wherever you like.
#
24. All Th Abs times:
=IF(A7="","",IF(AI7="","",SUMPRODUCT(--(C7="ab")+(G7="ab")+(L7="ab")+(N7="ab")+(R7="ab")+(V7="ab")+(AA7="ab")+(AC7="ab"))))
#
25. Alternative way for section wise
Roll No.
=IF(V3>45,V3-45&"
(B)",V3&" (A)")
#
26. Change all caps into first letter
only cap in a sentence: =upper(left(A1,1))&lower(right(A1,len(A1)-1))
#
27. Change all caps into first letter
only cap in each word: =proper(A1)
#
28. Copy only non blank cells
(horizontal): =LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(14:14,SMALL(IF($G$14:$Q$14<>"",COLUMN($G$14:$Q$14)),COLUMNS($G$14:G14)))))
#
29. Copy only non blank cells
(vertical): =LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(A:A,SMALL(IF($A$1:$A$15<>"",ROW($A$1:$A$15)),ROWS($B$1:B1)))))
#
30. Count if corresponding cells not
empty =COUNTIFS(D7:D115,">="&D5*0.4,B7:B115,"<>")
#
31. Count if greater than another cell =COUNTIF(D7:D115,">="&D5*0.4)
#
32. Decimal alignment:
=Select the
cell(s)> Opposite click > Format cells > Custom > In place of
"General", Type "0.????". After a whole number, there will
be a "."
#
33. Feedback in Remark: =IF(G25="1st",U23,IF(G25="2nd",U24,IF(G25="3rd",U25,IF(G25="-",U27,U26))))
#
34. Final Absent Times =IF(AS7="","",SUM(COUNTIF(INDIRECT({"AN7","AI7","AE7","Z7","U7","P7","L7","G7"}),"")))
#
35. Grade point average:
=IF(M17="","",M17/8)
#
36. Grade points: =IF(E16="","",IF(E16="A+",4,IF(E16="A",3.6,IF(E16="B+",3.2,IF(E16="B",2.8,IF(E16="C+",2.4,IF(E16="C",2,IF(E16="D+",1.6,IF(E16="D",1.2,0.8)))))))))
#
37. Grades =IF(E15="","",IF(E15/E13<0.2,"E",IF(E15/E13<0.3,"D",IF(E15/E13<0.4,"D+",IF(E15/E13<0.5,"C",IF(E15/E13<0.6,"C+",IF(E15/E13<0.7,"B",IF(E15/E13<0.8,"B+",IF(E15/E13<0.9,"A","A+")))))))))
#
38. Grades for 11 n 12: IF(OR(F11="",F11="Ab"),"",IF(F11=0,"N",IF(F11/F10<0.2,"E",IF(F11/F10<0.3,"D",IF(F11/F10<0.4,"D+",IF(F11/F10<0.5,"C",IF(F11/F10<0.6,"C+",IF(F11/F10<0.7,"B",IF(F11/F10<0.8,"B+",IF(F11/F10<0.9,"A","A+"))))))))))
#
39. Highlight exact match
=AND(NOT(ISBLANK(C7)),C7="Ab")
#
40. Hyperlink with a condition:
=IF(A26="a",HYPERLINK("[combined
result format pract.xlsx]'4 Mark Ledger'!B2","next"))
#
41. Insert file name in a cell =MID(CELL("filename",A1),SEARCH("[",CELL("filename",A1))+1,SEARCH(".",CELL("filename",A1))-1-SEARCH("[",CELL("filename",A1)))
#
42. Maximum of the cells only with roll
no. (array) =MAX(IF($B$7:$B$110<>"",D7:D110))
#
43. RanK (array)
=SUM(--ISNUMBER(MATCH(ROW(INDIRECT("1:"&RANK(N7,$N$7:$N$115))),RANK($N$7:$N$115,$N$7:$N$115),0)))
#
44. Rank (st, th, etc.) =X7&IF(AND(MOD(ABS(X7),100)>10,MOD(ABS(X7),100)<14),"th",CHOOSE(MOD(ABS(X7),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))
#
45. Score not more than a limit plus
some score: =MIN(23,C8/3+7)
#
46. Vlookup from a table:
=VLOOKUP(Q9,$T$5:$U$13,2,1)
# 47. sum same name scores
=IF(A2<>A1,SUMIF($A$2:$A$22,A2,$B$2:$B$22),"")
#
48. Don't allow duplicate values (Data
Validation)
Custom
> Formula >=COUNTIF($G$7:$G$6009,G7)=1 [range, first cell]
#
49. Count cells equal to or less than
another cell
=COUNTIFS(AE6:AE73,"<="&AI78)
#
50. Copy only unique values from a list
=INDEX(q,MATCH(0,COUNTIF($D$4:D4,q),0))
[Array for cell D5. q = named range of list.]
#
51. Copy cells ignoring error
=IFERROR(D5,"")
# 52. Copy value before decimal
=INT(F3)
# 53. Move cursor:
1.
from any cell to A1: Ctrl+Home
2.
from any cell to col. A/final col. in the same row or first row/last row in the
same col.: ctrl+arrow
# 54. Second largest/ Maximum, Second smallest/ Minimum value from a
list:
=Large(A1:A10,2)
=Small(A1:A10,2)
#
55. Highlight only "Ab" for
absent : conditional formatting
Select all, enter in dialogue box:
=AND(NOT(ISBLANK(A1)),A1="Ab")
#
56. Allow only a value less than or
equal to a cell and "Ab": Data validation
=OR(cell="Ab",AND(cell>=0,cell<=$C$7))
cell:
first cell in the column
#57.
Vlookup and sum from a column
=SUMIF($E:$E,P9,$I:$I)
vlookup the value in P9 in the
column E and if true, add the corresponding values in I.
#58.
Countifs with double criteria
=COUNTIFS(Details2!$B$7:$B$508,$D$7,Details2!$N$7:$N$508,G6)
=COUNTIFS(B7:B508,D7,N7:N508,G6)
If
column B has a value in cell D, count the values in column N that have a value
in cell G.
#59.
Dynamically extract a list of
unique values from a column range with formula
https://www.extendoffice.com/documents/excel/4032-excel-dynamic-list-of-unique-values.html
1. Select a blank cell such as
D2, enter formula =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1,
$B$2:$B$9), 0)),"") (B2:B9 is the column data which you want to
extract the unique values, D1 is the above cell where your formula is located)
into the Formula Bar, and then press the Ctrl + Shift + Enter keys simultaneously.
2. Keep selecting cell D2, then
drag the Fill Handle down to get all unique values from the specified range.
Now all unique values in column
range B2:B9 are extracted. When values in this range changed, the unique value
list will be dynamically changed immediately.
सोमे formulae :
IF(B3="","",IFERROR(ROUNDUP(MAX(10,MIN(23,SUM(D3,E3,F3))),0),""))
IFERROR(ROUNDUP(MIN(23,(MAX(10,D3+E3+F3/3+3))),0),"")
=ROUND(VLOOKUP($Y$5,Ledger!$A$9:$CA$107,75),1)&" %"
#60.
Count cells that contain errors (Array)
=SUM(IF(ISERROR(C10:M10),1))
#61.
Combine cell value/ reference in formula:
=IF('[new.xls]Student
Tracking'!A1)=$E$6, C9,"") can be changed into:
=IF(INDIRECT("'[new.xls]Student
Tracking'!"&G9)=$E$6, C9,"")
in
which cell G9 can have a value like: A1 for the first formula.
=INDIRECT(S4)
=IDDIRECT(S4&S5)
in which S4 may have column letter Q and S5 may have row number 1.
=vlookup(A1,Details!A1:Z25,5)
can be changed into:
=vlookup(A1,
#62.Convert
column letter into number
=COLUMN(INDIRECT(E2&"1")) [converts the letter typed in
E2 into number.]
#63.
Cell content as Vlookup array table
=VLOOKUP(D1,INDIRECT(A2),2,FALSE)
(text
in D1 works as array value)
(https://www.mrexcel.com/board/threads/vlookup-table-array-based-another-cells-value.786750/)
#64.
Count ignoring blanks with formula
=SUMPRODUCT(--(A1:A99<>””))
=IF(A1=””,””,SUMPRODUCT(--(A1:A99<>””)))
Or, type A in A1 and enter in A2: =CHAR(CODE(A1) + 1)
For Z AA AB:
=LEFT(ADDRESS(1, ROW(A1), 4, TRUE), (ROW(A1)>26)+1)
Or, :=SUBSTITUTE(ADDRESS(1,ROWS(A$1:A1),4),1,"")
For X Y Z A B :
=CHAR(MOD(ROW()-1,26)+65)
#66. Translate
#68. Numbers in a list
=Sequence(500)
Comments
Post a Comment