Look at this image below, Both formulaes: "=B3*D$2" & "=B4*$D$2" gives me same values in C Column.
BxVzui7.jpg
Both formulas are quite different.
Look at this image below, Both formulaes: "=B3*D$2" & "=B4*$D$2" gives me same values in C Column.
BxVzui7.jpg
Both formulas are quite different.
Last edited by kingston_123; 06-30-2020 at 08:36 AM.
Your attached picture doesn't say so ....
.
What are you on about?
1 x 5 = 5
2 x 5 = 10
The only difference between the two formulae is the value they are using from column B, otherwise they are identical.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I didn't understand difference.
B3 = 1
D2 = 5
B3*D2 = 1 x 5 = 5
B4 = 2
D2 = 5
B4*D2 = 2 x 5 = 10
* is the multiplication sign in Excel
Not so, as can be seen very clearly from your picture."=B3*D$2" & "=B4*$D$2" gives me same values in C Column
Last edited by AliGW; 06-30-2020 at 09:27 AM.
What I am saying is: Whether I use this "=B3*D$2" or "=B3*$D$2", below output is same:
B3 = 1
D2 = 5
B3*D2 = 1 x 5 = 5
B4 = 2
D2 = 5
B4*D2 = 2 x 5 = 10.
check out absolute and relative references
As you are filling down a column, rather than across a row, the D reference does not change. It would be the row number that changes but your formula has a $2 absolute reference to row.
That's not what you typed into post #1!!!
You are drag copying DOWN, so you only need to anchor the row, so both will work.
These will NOT work:
=B3*$D2
=B3*D2
I didn't lock D reference by $ symbol, then why it is not changing?
D is a column reference - when you drag copy down, that doesn't change, so there is no need to anchor it, but you MUST anchor the row (2). In your picture the row is anchored and both of your examples in post #1 have the row anchored. That's why it works.
Please Google "Absolute and relative cell references in Excel" and read up on it.
https://www.ablebits.com/office-addi...ference-excel/
post#1 first formulae says =B3*D$2. Here D is not locked (not anchored in your words)
No, it doesn't need to be.
How to visualize the locked reference?
Type =AA2 into cell A1.
Drag copy across and down and inspect the results.
Then do the same with =AA$2, =$AA2 and =$AA$2 in turn.
Also follow the link I gave.
That's it from me - nothing more I can do to help.
I have yet to see any word of gratitude from you to any of those who have offered you help over the past few days. This really is very discourteous of you.
Last edited by AliGW; 06-30-2020 at 10:13 AM.
Look below the results of D$2, $D2 and $D$2
Attachment 684760
Those results are correct.
I am just asking simple question, why D$2, $D2 are giving me same results?
It has been answered several times. Please review posts #7, 8, 10 and 14 in particular.
These give the same results: D$2, $D$2
These do not: D2, $D2
Last edited by AliGW; 06-30-2020 at 10:39 AM.
An unanchored column will change if you copy the formula to a NEW column. In your example, you are copying to the same column and therefore it doesn't change. Therefore it does not have to be anchored (either way, D will remain D since you didn't copy across columns).
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
As I said in post #10:
D is a column reference - when you drag copy down, that doesn't change, so there is no need to anchor it, but you MUST anchor the row (2).
Marked as solved and still not one word of thanks to any of us. Shocking!
Thank you.
At last - first time this week! Wasn't hard, was it?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks