+ Reply to Thread
Results 1 to 23 of 23

Why does this 2 formulas gives me same result? (Excel formula Basics)

  1. #1
    Registered User
    Join Date
    06-17-2020
    Location
    USA
    MS-Off Ver
    Office 2013
    Posts
    50

    Why does this 2 formulas gives me same result? (Excel formula Basics)

    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.

  2. #2
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,133

    Re: Why does this 2 formulas gives me same result? (Excel formula Basics)

    Your attached picture doesn't say so ....

    .

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Why does this 2 formulas gives me same result? (Excel formula Basics)

    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.

  4. #4
    Registered User
    Join Date
    06-17-2020
    Location
    USA
    MS-Off Ver
    Office 2013
    Posts
    50

    Re: Why does this 2 formulas gives me same result? (Excel formula Basics)

    I didn't understand difference.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Why does this 2 formulas gives me same result? (Excel formula Basics)

    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

    "=B3*D$2" & "=B4*$D$2" gives me same values in C Column
    Not so, as can be seen very clearly from your picture.
    Last edited by AliGW; 06-30-2020 at 09:27 AM.

  6. #6
    Registered User
    Join Date
    06-17-2020
    Location
    USA
    MS-Off Ver
    Office 2013
    Posts
    50

    Re: Why does this 2 formulas gives me same result? (Excel formula Basics)

    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.

  7. #7
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Why does this 2 formulas gives me same result? (Excel formula Basics)

    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.
    Cheers
    Andy
    www.andypope.info

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Why does this 2 formulas gives me same result? (Excel formula Basics)

    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

  9. #9
    Registered User
    Join Date
    06-17-2020
    Location
    USA
    MS-Off Ver
    Office 2013
    Posts
    50

    Re: Why does this 2 formulas gives me same result? (Excel formula Basics)

    I didn't lock D reference by $ symbol, then why it is not changing?

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Why does this 2 formulas gives me same result? (Excel formula Basics)

    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/

  11. #11
    Registered User
    Join Date
    06-17-2020
    Location
    USA
    MS-Off Ver
    Office 2013
    Posts
    50

    Re: Why does this 2 formulas gives me same result? (Excel formula Basics)

    post#1 first formulae says =B3*D$2. Here D is not locked (not anchored in your words)

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Why does this 2 formulas gives me same result? (Excel formula Basics)

    No, it doesn't need to be.

  13. #13
    Registered User
    Join Date
    06-17-2020
    Location
    USA
    MS-Off Ver
    Office 2013
    Posts
    50

    Re: Why does this 2 formulas gives me same result? (Excel formula Basics)

    How to visualize the locked reference?

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Why does this 2 formulas gives me same result? (Excel formula Basics)

    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.

  15. #15
    Registered User
    Join Date
    06-17-2020
    Location
    USA
    MS-Off Ver
    Office 2013
    Posts
    50

    Re: Why does this 2 formulas gives me same result? (Excel formula Basics)

    Look below the results of D$2, $D2 and $D$2

    Attachment 684760

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Why does this 2 formulas gives me same result? (Excel formula Basics)

    Those results are correct.

  17. #17
    Registered User
    Join Date
    06-17-2020
    Location
    USA
    MS-Off Ver
    Office 2013
    Posts
    50

    Re: Why does this 2 formulas gives me same result? (Excel formula Basics)

    I am just asking simple question, why D$2, $D2 are giving me same results?

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Why does this 2 formulas gives me same result? (Excel formula Basics)

    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.

  19. #19
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Why does this 2 formulas gives me same result? (Excel formula Basics)

    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

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Why does this 2 formulas gives me same result? (Excel formula Basics)

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

  21. #21
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Why does this 2 formulas gives me same result? (Excel formula Basics)

    Marked as solved and still not one word of thanks to any of us. Shocking!

  22. #22
    Registered User
    Join Date
    06-17-2020
    Location
    USA
    MS-Off Ver
    Office 2013
    Posts
    50

    Re: Why does this 2 formulas gives me same result? (Excel formula Basics)

    Thank you.

  23. #23
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Why does this 2 formulas gives me same result? (Excel formula Basics)

    At last - first time this week! Wasn't hard, was it?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Formulas to pick up result
    By Jamidd1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-27-2017, 06:16 PM
  2. Formulas as a result of Vlookup
    By xfish in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-26-2016, 12:11 PM
  3. How to use some formulas to search result ?
    By jayshing in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-10-2015, 11:54 PM
  4. not print result formulas
    By max_max in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-05-2015, 11:53 AM
  5. Replies: 5
    Last Post: 01-31-2013, 12:55 PM
  6. [SOLVED] Combining two formulas to get one result
    By jaycee66 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-24-2012, 06:47 PM
  7. Two identical formulas for a different result?
    By hochedez in forum Excel General
    Replies: 5
    Last Post: 09-08-2005, 10:43 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1