Excel doesn't provide MEDIANIF and MODEIF. You will have to use Array formulas to achieve these functionality. Let's assume that our data is like below – To calculate MEDIANIF and MODEIF, enter below formulas i.e. not by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. This will put { } brackets around the formula…
Category: Tips and Tricks
Tips and Tricks 73 – Use Conditional Formatting to Highlight Duplicate Cells
Suppose your data is in range A2:A100 and you want to highlight all those cells which are duplicates. 1. Select A2:A100 and Home Tab > Conditional Formatting > New Rule 2. Put following formula after clicking "Use a formula to determine which cells to format" =COUNTIF($A$2:$A$100,A2)>1 3, Click on Format Button to format the cells…
Tips & Tricks 72 – Sum Every Nth Row
If your numbers are in range A1:A100, use below formula =SUMPRODUCT((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,2)=0)) Above formula is for every 2nd row. Replace 2 with N. Hence, for every 5th row – =SUMPRODUCT((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,5)=0)) This is a generic formula and will work for any range. If you range is B7:B50, your formula would become =SUMPRODUCT((B7:B50)*(MOD(ROW(B7:B50)-ROW(B7)+1,2)=0))
Tips & Tricks 71 – Sum Every Odd Row
If your range is A1:A100, use following formula. =SUMPRODUCT((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,2)<>0)) This is a generic formula, hence if your range is B7:B50, your formula will become =SUMPRODUCT((B7:B50)*(MOD(ROW(B7:B50)-ROW(B7)+1,2)<>0))
Tips & Tricks 70 – Sum Every Even Row
If your range is A1:A100, use following formula. =SUMPRODUCT((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,2)=0)) This is a generic formula, hence if your range is B7:B50, your formula will become =SUMPRODUCT((B7:B50)*(MOD(ROW(B7:B50)-ROW(B7)+1,2)=0))
Tips & Tricks 69 – Sum Top N values in a Range
Suppose you have numbers in range A1:A100 and you want to sum up top N values =SUMPRODUCT(LARGE($A$1:$A$100,ROW(1:10))) In case, you want to ignore 0 values (and blanks) =SUMPRODUCT(LARGE(IF($A$1:$A$100<>0,$A$1:$A$100),ROW(1:10))) Both the above formulas will function only if there are at least N values as per ROW(1:N). Hence, for above formulas, it would work only if there…
Tips & Tricks 68 – Sum Bottom N Values in a Range
Suppose you have numbers in range A1:A100 and you want to sum up bottom N values =SUMPRODUCT(SMALL($A$1:$A$100,ROW(1:10))) In case, you want to ignore 0 values (and blanks) =SUMPRODUCT(SMALL(IF($A$1:$A$100<>0,$A$1:$A$100),ROW(1:10))) Both the above formulas will function only if there are at least N values as per ROW(1:N). Hence, for above formulas, it would work only if there…
Tips & Tricks 67 – Show the Complete Data in Chart even though Data is Filtered
By default, charts in excel show filtered data only. To show complete data i.e. to ignore filtering – 1. Right click on Chart and click on Select Data. 2. Click on Hidden and Empty Cells at the bottom 3. Select Show data in hidden rows and columns. Now, you will have the result which you…
Tips & Tricks 66 – Show the Complete Area of a Worksheet as only Limited Area is being shown
If you can see only limited area of a worksheet 1. Check if the sheet is protected. This you check by going to Review tab and see if Unprotect Sheet Button is appearing. – If Unprotect Sheet Button is appearing sheet is protected. You need to click it and if password is asked, you need…
Tips & Tricks 65 – Show a Limited Area of Worksheet
You have a worksheet and you want to show only a limited area to users. Say you want them to see only A1:G50 and remaining you don't want to show them. Use following steps – 1. Click column H so that entire column is selected. Now CTRL+SHIFT+Right Arrow key so that all columns will get…