Question about Microsoft Office Standard for PC

# Excel formula 1- information about "relative cell reference" 2- "absolute cell reference" 3-"mixed cell reference" 4-"sspecific order of formulas" 5"working with advance formulas"

Posted by on

• Anonymous Mar 21, 2014

I have this formula in cell G2
=IF(Week!\$F\$11=Week!\$O26, SUM(Week!\$M\$12:\$M\$21))

When I copy it to cell H2, the absolute cell references copy correctly, but the mixed reference - \$O26 - remains the same. I thought it would change to \$O27. Or is this because I am copying horizontally instead of vertically?

×

• Level 1:

An expert who has achieved level 1.

Problem Solver:

An expert who has answered 5 questions.

• Contributor

Relative cell reference is address of cell , when copied from one cell to another gets changed automatically.

e.g. Put formula in cell c5 as A5+1 when you copy this from c5 to c6 the address of A5 will automatically changed to A6.

Absolute Cell Reference

As above now put formula in cell C5 as \$A\$5+1 ,now copy this formula from C5 to C6 if you this formula youwill find cell Address of A5 does not changed.

Mixed Cell Reference:

If we put Dollar (\$) sign before Alphabetic cell address i.e.\$c5 then even if you copy this formula in any cell , coloumn (c)  will remains constant.

Similarly if we put Dollar (\$) sign before Numeric cell address i.e.c\$5  then you copy this formula in any cell row address of the cell remains same.(5).

Pl elobarate on remaining two points 'Specific order of formulas ' and 'advance formulas,

Posted on Oct 16, 2007

Hi,
A 6ya expert can help you resolve that issue over the phone in a minute or two.
Best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.
The service is completely free and covers almost anything you can think of (from cars to computers, handyman, and even drones).
Good luck!

Posted on Jan 02, 2017

×

my-video-file.mp4

×

## Related Questions:

### When i copy a sum formula in excel the first cell does not remain the same it increments by 1. how can I prevent this?

There are 2 types of direct cell references that you can use when you're writing formulas: Relative References & Absolute References.
A Relative Reference is the address of a cell (e.g. A5). When a Relative Reference in a formula is copied from one cell to another, the Reference gets changed automatically. e.g. If you put a formula in cell c5 as A5+1, when you copy this from c5 to c6 the formula A5+1 will automatically change to A6+1.
An Absolute Cell Reference does not change when its copied to another location. As in the example above if the formula in cell C5 is written as \$A\$5+1, if you copy this formula from C5 to C6 it will remain as \$A\$5+1 (NOT change to \$A\$6+1.
The \$ sign signifies Absolute, and can be applied to the Row reference, the Column reference, or both Column & Row (as in the example).

Oct 17, 2014 | Microsoft Excel for PC

### Why don't formulas copy from cell to cell?

Step 1
Determine what formula is to be copied and to what location it is to be copied. Carefully consider the cells that are to be referenced in the formula and that they are indeed the cells that contain the information to be acted upon by this formula.
Step 2
Highlight a cell that has the formula in it and click the right mouse button. Select the "Copy" option.
Step 3
Move to the new destination cell for this formula, right click the mouse button and select "Paste."
Step 4
Check the first cell to make sure the correct calculation has been performed. If not, check the formula that appears in the cell that contains the result of the formula that was just copied. Make sure that the formula references the correct cells. If not, correct the formula and then copy the corrected formula to the rest of the cells that should have this formula applied to them.

The only time I have seen this not work is when the cells/pages are protected.

Note:
Some formulas will require references to multiple pages or a workbook. Make sure that the absolute cell address is used for specific numbers and those that are to be applied to subsequent cell addresses are clearly denoted by the use of "\$" in the cell address.

Dec 17, 2009 | Microsoft Office Home and Student 2007...

### How can I use a relative address in the @find function? It changes to a fixed address when I stop editing.

When you include addresses like A2, C10, F15 they are relative addresses .eg: If you have entries like:
in B4 you have saturday
in C6 you have the formula =FIND("a",B4) .....the answer is 2
B4 is a relative cell address, because if you copy the formula across the row, OR up/down the column, the formula changes and the answer changes
To make it an absolute address you have to type a \$ in the cell address. If you type the cell address formula as \$B4, it means wherever you move to or copy the formula the B will not change
If you type B\$4 the row no 4 is ABSOLUTE or fixed.
\$B\$4, both column B and row 4 are fixed. If you move the formula anywhere the cell reference will not change.
Summary B4 is a relative cell address
\$B4 is absolute address (B is fixed)
B\$4 is absolute address (4 is fixed)
\$B\$4 is abso;lute address (Bis fixed, 4 is also fixed)
luciana44

Sep 05, 2009 | Corel WordPerfect Office X4 Home & Student...

### Need to know the formula for increasing a \$3500 revenue by 0.005% per month for 24 consequtive months. how do i preform this with one formula to avoid entering it 24 different times. i know that the answer...

If the column is absolute, then use the \$ before the first character and if the row is absolute use the \$ before the second character in your cell designation. If BOTH column and row are absolute, use the \$ before both the column and row character.

Examples: \$A1, A\$1, \$A\$1

Mar 30, 2009 | Microsoft Excel for PC

### Circular reference working how to open excel file

Clicking cancel will invalidate the formulae, circular references refer to the dependant and precedent cells using each other.

Track/ Audit depending on your Excel version to show which formulae is incorrect - or options view, tick show formaulae and remove them.

Nov 20, 2008 | Microsoft Excel for PC

### BASIC EXCEL FORMULA

Are you looking to solve any particular problem?--- because there are a huge number of possible formulas in Excel.

However, in my opinion, the most commonly needed ones are addition, subtraction, division, multiplication, and summing.

columns: A B C D
rows
1 20 3
2 10 4
3 15 2
4 1 2 3

Then suppose you type in the following formulas (in the D column):

columns: A B C D
rows
1 20 3 =A1+B1
2 10 4 =A2-B2
3 15 2 =A3*B2
4 1 2 3 =sum(A4:C4)

Then the following answers will appear in the D column:

columns: A B C D
rows
1 20 3 23
2 10 4 6
3 15 2 30
4 1 2 3 6

Sep 29, 2008 | Microsoft Computers & Internet

### Percentages in Excel

No problem, Melinda, I am here to help!

A B C D
1 Question Yes No Total
2 Is sky blue? 20 2 22
3 Is world round? etc.

In this case, the formula for % of Yes would be: =B2/D2. This would give you a decimal point result such as 0.909091. Now if you want to make this look like a percentage in your spreadsheet, just do the following:
1) click on the cell where you have the division formula
2) clck on Format in the top menu bar
3) click on Cells
4) click on the Number tab (if you're not already there)
5) click on Percentage in the list of categories
6) click OK

To boil it all down to a simple principle, percentages are created in Excel by dividing the two numbers using a formula with "/" in it, and then formatting the result to look like a percentage instead of a decimal.

I might have misunderstood your question, and I have an idea of what else you might have been asking (and another slightly more complicated solution for it!), so please let me know if my first answer didn't hit the mark!

Good Luck!
Regards,
RichMTech

Aug 08, 2008 | Microsoft Excel for PC

### Regarding Excel formulas

Yes, its possible by designing a formula in excel.

Aug 03, 2008 | Microsoft Windows XP Professional

### Formulas

meri excel file ka security paasward missing. Pls i want track the paasward.

Jul 13, 2008 | Microsoft Excel for PC

### Excel argument

If I understand correctly, you want to figure the normal wages at 40 hours and less. If there is more then 40 hours, calculate the normal 40 hours, then calculate the hours overtime (time and a half) and add them to get a total.

B5 columns are filled with this:
=IF(A5>\$B\$1,(\$B\$2*\$B\$1)+((A5-\$B\$1)*(\$B\$3)),A5*\$B\$2)

NOTE: The \$ sign doesn't refer to money, it refers to an absolute reference so when copying a formula, the cell references with a \$ sign are fixed and don't adjust according to the relative position to where it's being pasted.

Oct 22, 2007 | Microsoft Excel 2003 (06503995)

## Open Questions:

#### Related Topics:

616 people viewed this question

Level 3 Expert

Level 3 Expert