## Article 40 - Order of Operations in Formula

Follow the **BODMAS** rule

**B** – Brackets (Parentheses)

**O** – Orders (Powers/Exponents)

**D** – Division

**M** – Multiplication

**A** – Addition

**S** – Subtraction (more…)

Sat 18
Jun 2016

Follow the **BODMAS** rule

**B** – Brackets (Parentheses)

**O** – Orders (Powers/Exponents)

**D** – Division

**M** – Multiplication

**A** – Addition

**S** – Subtraction (more…)

Mon 23
May 2016

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

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

Sat 06
Feb 2016

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

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.

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

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

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.**