Sat 18 Jun 2016

Article 40 - Order of Operations in Formula

By |Saturday, June 18th, 2016|Categories: Articles|Tags: , , , , , , , , , , , , , , |0 Comments

Follow the BODMAS rule

B – Brackets (Parentheses)
O – Orders (Powers/Exponents)
D – Division
M – Multiplication
A – Addition
S – Subtraction (more…)

Mon 23 May 2016

Solution - Challenge 41 – Sum the Maximum Number where duplicates Exist

By |Monday, May 23rd, 2016|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a possible solution to the challenge Challenge 41 – Sum the Maximum Number where duplicates Exist

Enter the below formula as Array Formula i.e. not by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.

=SUM((MATCH(A2:A10,IF(COUNTIFS(A2:A10,A2:A10,B2:B10,">"&B2:B10)=0,
A2:A10),0)=ROW(A2:A10)-MIN(ROW(A2:A10))+1)*B2:B10)

The workbook illustrating the above solution can be downloaded from Solution - Challenge 41 - Sum the Maximum Number where duplicates Exist

Sat 14 May 2016

Tips & Tricks 136 - Quickly Copy a Formula or a Value in a Large Number of Cells

By |Saturday, May 14th, 2016|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

Say, you have written a formula (or put a value) in cell B1 and you want to fill in this formula till B10000. You can drag the formula but higher the number of cells, more tedious it becomes. Fortunately, we have a way to do this very fast.

1. Put formula in B1.
2. CTRL+C of B1
3. CTRL+G and put the range say B2:B10000 in Reference: box (or even B1:B10000 as copying B1 in B1 will not cause any problem)
4. Press Enter

1

Sat 06 Feb 2016

Tips & Tricks 129 - Generate Non Repeating Random Numbers through Formula

By |Saturday, February 06th, 2016|Categories: Tips and Tricks|Tags: , , , |0 Comments

Suppose, you want to generate non-repeating random numbers between 1 to 30, you can use following formula in A2 and drag down

=IFERROR(AGGREGATE(14,6,ROW($1:$30)*NOT(COUNTIF($A$1:$A1, ROW($1:$30))),
RANDBETWEEN(1,30-ROWS($1:1)+1)),"")

Note: $A$1:$A1 is with reference to A2 as you put formula in A2 and dragged down. Suppose, you had put the formula in G4, this should be replaced with $G$3:$G3.

If your starting and ending numbers are in B1 and C1, use below formula

=IFERROR(AGGREGATE(14,6,ROW(INDIRECT($B$1&":"&$C$1))*
NOT(COUNTIF($A$1:$A1,ROW(INDIRECT($B$1&":"&$C$1)))),
RANDBETWEEN($B$1,$C$1-ROWS($1:1)+1)),"")

For versions, prior to 2010 following basic construct can be used (Build error handling depending upon the version. For example, Excel 2007 will support IFERROR whereas 2003 supports ISERROR) -

=LARGE(INDEX(ROW($1:$30)*NOT(COUNTIF($A$1:$A1, ROW($1:$30))),,),
RANDBETWEEN(1,30-ROW(A1)+1))

Sat 23 Jan 2016

Tips & Tricks 128 - Used F9 to See Values in the Formula but Values Stick / Formula doesn't gets Restored

By |Saturday, January 23rd, 2016|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

We know that great trick that you can select part of the formula and see it values by pressing F9. See the below snip where I have selected part of the formula (see shaded area) and pressed F9 to see its values.

1

Now, if I press enter that part of the formula gets converted to values and formula doesn't get restored. (see the below snip)

1

How to Prevent this

1. If you have not pressed Enter, press ESC after using F9 to see its values and formula will get restored.

2. If you have pressed Enter, press CTRL+Z (Undo) and formula will get restored. (You need to press CTRL+Z after pressing enter. If there have been many intermediate steps, you need to press CTRL+Z repeatedly to undo this step. Of course, CTRL+Z has its own limitations in terms of retracing steps)

Sat 04 Jul 2015

Article 17 - 3D Formulas - Hidden Wonder of Excel

By |Saturday, July 04th, 2015|Categories: Articles|Tags: , , , , , |0 Comments

3D formulas are one of the hidden wonders of Excel and not many of us know about the secret of this. The purpose of this article is to unravel the mystery of 3D formulas in Excel.

Suppose you have 4 worksheets called Quarter1, Quarter2, Quarter3, Quarter4 as below. And you want to find the total of Quarter1 to Quarter4 revenue. This is quite simple a task, just put the formula =Quarter1!B6+Quarter!B6+Quarter3!B6+Quarter4!B6 and you are through. But what happens when number of sheets are large say 12, 50 or 100. This becomes quite cumbersome to enter. And that is where the magic of 3D formulas comes into picture.

(more…)