Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Highest Value

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a spreadsheet that tracks my investments. I would like to add cells that "remember" the "high water mark" obtained by various investments.

    Given:
    A1 curent Value
    B1 Highest Value

    How do I set up

    IF A1 > B1 then B1 = A1

    The circular reference errors are making me crazy.

    Bob

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,063
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-05-11 08:45, rwcs wrote:
    I have a spreadsheet that tracks my investments. I would like to add cells that "remember" the "high water mark" obtained by various investments.

    Given:
    A1 curent Value
    B1 Highest Value

    How do I set up

    IF A1 > B1 then B1 = A1

    The circular reference errors are making me crazy.

    Bob
    Are you computing/retrieving the current and the highest values or entering them manually in A1 and B1 respectively?

  3. #3
    New Member
    Join Date
    May 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The initial concept was that A1 would be a sum function (sum of all individual investments). But it would be nice to be able to apply this to investment elements, which would include manually entered values.

  4. #4
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Have you looked at using the Max function in B1 ?

  5. #5
    New Member
    Join Date
    May 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I've looked at a bunch of functions:

    Specificaly
    If B1 is =MAX(A1) it performs no differently than =A1
    IF B1 is =MAX(A1:B1) result is always Zero (0) (Same result =MAX(A1,B1))

    Do you have another MAX statement that works?

    Bob

  6. #6
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I was thinking of having the Max function pointing at the various investments . For example if all your investments were in row A then place MAX(A:A) in the B1 cell.
    If they were in specific cells then =MAX(A1,A21,A33).

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,063
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-05-11 09:08, rwcs wrote:
    The initial concept was that A1 would be a sum function (sum of all individual investments). But it would be nice to be able to apply this to investment elements, which would include manually entered values.
    I'd advise, if applicable, retrieving the current value in A1 and computing the highest value in B1. That would look like this:

    In A1 enter:

    =OFFSET(Investments!C1,MATCH(9.99999999999999E+307,Investments!C:C)-1,0,1,1)

    In B1 enter:

    =MAX(Investments!C:C)

    The first formula retrieves last entered investment value in column C in sheet Investments.

    The second formula computes the highest value from all values entered in column C in sheet Investments.

    If you'd like to use the SUM function in A1, then:

    In A1 enter:

    =SUM(Investments!C:C)

    In B1 enter:

    =MAX(A1,MAX(Investments!C:C))

    although I don't see what the result of the latter would possibly mean.

    If you insist entering both the Current Value and the Highest Value in A1 and B1, you'll need VBA code, which the VBA army around this board can give you.

    Aladin

  8. #8
    New Member
    Join Date
    May 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have investment "A" lets say the value today is $100. Tomorrow I got to my investment site and see the value has risen to $105, so I record that in my spreadsheet. The day after my investment has dropped to $103, so I record the value in my spreadsheet. How do I know on day 3 what the highest value of this investment has been in the past. What I would like to see:

    Day 1: A1 [$100] B1 [$100]
    Day 2: A1 [$105] B1 [$105]
    Day 3: A1 [$103] B1 [$105]

    Is this possible?

    Thanks
    Bob

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,063
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-05-11 09:57, rwcs wrote:
    I have investment "A" lets say the value today is $100. Tomorrow I got to my investment site and see the value has risen to $105, so I record that in my spreadsheet. The day after my investment has dropped to $103, so I record the value in my spreadsheet. How do I know on day 3 what the highest value of this investment has been in the past. What I would like to see:

    Day 1: A1 [$100] B1 [$100]
    Day 2: A1 [$105] B1 [$105]
    Day 3: A1 [$103] B1 [$105]

    Is this possible?

    Thanks
    Bob
    Bob,

    The above scenario doesn't have any memory. How do you suppose to know the highest value at the nth day? I can't, can you?

    What I'd suggest is illustrated with sheets that follow:

    Microsoft Excel - InvestmentsAdm.xls
    File(F) Edit(E) View(V) Insert(I) Tool(T) Data(D) Window(W) Help(H)
    A1=Day
    ABCD
    1DayValue
    21:alert('100')>$100.00
    32:alert('105')>$105.00
    43:alert('103')>$103.00
    5
    6
    Investments

    You can see the formula of cells only click each above hyperlinks

    The above image was automatically written by excel VBA. If you want this code, click here and I'll email the file to you.


    Microsoft Excel - InvestmentsAdm.xls
    File(F) Edit(E) View(V) Insert(I) Tool(T) Data(D) Window(W) Help(H)
    A1=Current Value
    ABCD
    1Current Value:alert('=OFFSET(Investments!$B$1,MATCH(9.99999999999999E+307,Investments!B:B)-1,0,1,1)')>$103.00
    2Highest Value:alert('=MAX(Investments!B:B)')>$105.00
    3
    4
    Overview

    You can see the formula of cells only click each above hyperlinks

    The above image was automatically written by excel VBA. If you want this code, click here and I'll email the file to you.


    The Overview sheet will inform you automatically about the state of your investments as long as you enter the daily values in the sheet Investments which is in the same workbook as the sheet Overview.

    What do you think?


    [ This Message was edited by: Aladin Akyurek on 2002-05-11 10:19 ]

  10. #10
    New Member
    Join Date
    May 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here is a bruit force macro method:

    Sub Auto_Open()
    x = Worksheets("Sheet1").Range("A1").Value
    y = Worksheets("Sheet1").Range("B1").Value
    If x > y Then y = x
    Worksheets("Sheet1").Range("B1").Value = y
    End Sub

    Is there an easier way?
    Bob

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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