+ 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
    43

    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
    Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkey
    MS-Off Ver
    Office 2010 - 32 Bit
    Posts
    284

    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
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    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!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

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

    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
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    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
    43

    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
    19,226

    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
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    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
    43

    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
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    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
    43

    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
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    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
    43

    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
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    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
    43

    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
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    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
    43

    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
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    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
    12,775

    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
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    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
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    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
    43

    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
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    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