+ Reply to Thread
Results 1 to 13 of 13

How do I get a formula in one cell to read the results of a formula in another cell?

  1. #1
    Registered User
    Join Date
    04-08-2014
    Location
    Montana
    MS-Off Ver
    Excel 2010
    Posts
    7

    How do I get a formula in one cell to read the results of a formula in another cell?

    I have this formula in cell D8: =IF(ISBLANK(C8),"",IF(AND(C8<=G8)*C8>=F8,"PASS","FAIL"))

    I want the formula in D8 to calculate its result based on the result of a formula in F8 and G8. Can this be done?

    The formula in F8 is =IF(ISBLANK(C8),"",IF(AND(G3=250)*G4=0.5,"0.484",""))

    The formula in G8 is =IF(ISBLANK(C8),"",IF(AND(G3=250)*G4=0.5,"0.516",""))

  2. #2
    Forum Contributor
    Join Date
    11-10-2017
    Location
    INDIA
    MS-Off Ver
    365
    Posts
    129

    Re: How do I get a formula in one cell to read the results of a formula in another cell?

    Hehe its easy replace the value of F8 with the formula in F8 and same with G8,

    Please Login or Register  to view this content.
    *Mark Solved if worked , else attach a raw sheet to investigate your requirement.

  3. #3
    Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    103

    Re: How do I get a formula in one cell to read the results of a formula in another cell?

    Hi

    I believe your problem is that your current formula in cell [D8] is assuming the results in cells [F8] and [G8] are numeric.
    But they are not, because you have them as text values "0.484" and "0.516" etc etc etc

    So try this:
    (Note: I have removed the text quotation marks surrounding 0.484 and 0.516)...
    in F8 : =IF(ISBLANK(C8),"",IF(AND(G3=250)*G4=0.5,0.484,""))
    in G8: =IF(ISBLANK(C8),"",IF(AND(G3=250)*G4=0.5,0.516,""))

    Then..
    In cell D8: =IF(ISBLANK(C8),"",IF(AND(C8<=G8,C8>=F8),"PASS","FAIL"))

    Please let me know if this sorts it out for you
    zeddy

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    48,585

    Re: How do I get a formula in one cell to read the results of a formula in another cell?

    If I understand you correctly, you actually do not have a problem there at all. Excel formulas will always "see" what a cell displays, not it's actual contents (eg a formula)

    So if D8 is referencing F8, it does not matter if F8 contains an entry or a formula, D8 will "see" whatever F8 displays
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    103

    Re: How do I get a formula in one cell to read the results of a formula in another cell?

    Hi Ford

    I would suggest that the purpose of the formula in cell [D8] is clearly to test whether a value in cell [C8] is within the lower and upper limits of a value range given in cells [F8] and [G8] respectively. The test would give either a PASS or FAIL.

    With the original formulas as quoted in post#1, there could NEVER be a result of PASS in cell [D8]
    ..because the lower/upper limits being checked against were NOT numeric values. They were text values.

    We'll ignore the misuse of AND in the [F8] and [G8] formulas, probably meant to use..
    [F8] =IF(ISBLANK(C8),"",IF(AND(G3=250,G4=0.5),0.484,""))
    [G8] =IF(ISBLANK(C8),"",IF(AND(G3=250,G4=0.5),0.516,""))

    Excel formulas will always "see" what a cell displays
    For someone who has lots of green blobs, you would know that you shouldn't use excel formulas to test against "what is displayed" since the 'displayed' value of a cell doesn't always match up with the actual cell value.

    For example, =IF(G5="$5.00","paid","owing") will NEVER show "paid" even if cell G5 shows as $5.00 (i.e. formatted as currency etc etc etc) because this formula is trying to compare a text value with a numeric value. Just saying.

    zeddy

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    48,585

    Re: How do I get a formula in one cell to read the results of a formula in another cell?

    zzzeddy you make a very valid point (I should have actually read the formulas to see what they were doing, rather than just the question)

    I think however, you are missing my point - formula1 that references a formula2 (even those examples you mention) STILL displays the answer for formula2, not the actual contents of the cell that contains formula2

  7. #7
    Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    103

    Re: How do I get a formula in one cell to read the results of a formula in another cell?

    Hi Ford

    I did understand the point you were making.
    I was just being annoying and making a point about what Excel "sees" and what is "displayed".
    Try this in your Excel2016:
    [A1] = TODAY()
    [B1] = 5
    [C1] = 10
    [D1] = IF($B$1<$C1,TODAY()-7,FORMULATEXT($A$1))

    ..so what do you see in cell [D1] if you change [B1] from 5 to 12?

    Hopefully, the original poster from Montana has the answer they were looking for.
    Well, regarding the Excel formula that is.

    zeddy

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    34,123

    Re: How do I get a formula in one cell to read the results of a formula in another cell?

    I'm with Ford on this: if you change B1 to 12, D1 is STILL showing the result of the formula in D1, which will return the text version of the formula in A1, NOT the formula itself.
    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.

  9. #9
    Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    103

    Re: How do I get a formula in one cell to read the results of a formula in another cell?

    Hi

    You are beginning to see the confusion then.
    What you are saying is if D1 contains a formula, then it will show the result of the formula.
    Yes, that's what Excel does.

    Ford says "formula1 that references a formula2 (even those examples you mention) STILL displays the answer for formula2"

    So the D1 (formula1) references a cell, A1, that contains a formula (formula2).

    But the 'answer' for formula2 (in A1) can be shown in cell A1 in many ways, depending on how you have formatted the display option for cell A1, i.e. the 'answer' for formula2 can show as 18/01/2020 or 18-Jan-2020 or 43848 or 01/18/20 etc etc etc

    And if you put a 12 in cell B2 then according to Ford "formula1 that references a formula2 (even those examples you mention) STILL displays the answer for formula2". But formula1 does not display the answer for formula2. Because the answer for formula2, as shown in A1, is a date value.

    zeddy

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    34,123

    Re: How do I get a formula in one cell to read the results of a formula in another cell?

    I think you are muddling cell formatting with cell values. Formatting is just a mask, usually for the user's benefit: Excel sees the underlying value.

  11. #11
    Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    103

    Re: How do I get a formula in one cell to read the results of a formula in another cell?

    You:
    Excel sees the underlying value
    - I agree

    Ford (post#4) :
    D8 will "see" whatever F8 displays
    (my italics)

    zeddy

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    34,123

    Re: How do I get a formula in one cell to read the results of a formula in another cell?

    Ah, I see what you mean - yes.

  13. #13
    Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    103

    Re: How do I get a formula in one cell to read the results of a formula in another cell?

    No criticism of Ford intended - it's just that the English language is tough enough as it is.
    It's the only one I know where "fat chance" means the same as "slim chance"

    zeddy

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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