Conditional Formula
VBA Telemetry pings you when your VBA projects fail
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Conditional Formula

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hi all...

    I am looking for a formula which calculates dependent upon the rest of the columns. Here's what the headers in the spreadsheet looks like:

    1st|Mth|2nd|Int|Hit|A/B|Bar|Low|High|Kill|Amt|Saved

    If under "Bar" TO is typed then "Amt" is equal to "Saved"

    If under "Bar" ANN is typed then set Saved to *

    If under "A/B", A is typed and in Bar "KRO" is typed then Saved equals to the absolute ("High" - "Hit")*(Amt)/(High)

    If under "A/B", B is typed and in Bar "KRO" is typed then Saved equals to the absolute ("Low" - "Hit")*(Amt)/(Low)


    I would really appreciate any help. And I hope the above makes sense!

    Many Thanks in advance, Ben



  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think this can easily be done, but I think the columns/directions need to be clearer, can you try again please?

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for your reply.

    The headers of the spreadsheet are as follows (with "|" as a separator):

    1st | Mth | 2nd | Int | Hit | A/B | Bar | Low |High | Kill | Amt | Saved

    If under the header, Bar, the value "TO" is typed then the header, Amt = Saved

    If under the header, Bar, the value "ANN" is typed then the header, Saved = Null

    If under the header, A/B, the value "A" is typed and in Bar, "KRO" is typed then the header, Saved = ABS("High"-"Hit")*("Amt")/("High")

    If under the header, A/B, the value "B" is typed and in Bar, "KRO" is typed then the header, Saved = ABS("Low"-"Hit")*("Amt")/("Low")

    FYI - Headers: 1st, Mth, 2nd - are dates.

    Int, A/B, Bar, Kill - are strings

    The rest are all numerics.

    So, for example, if A/B is "A", Bar is "KRO", High is "140", Hit is "130" and Amt is "50" then:

    140 - 130 = 10,
    now 10 * 50 = 500
    then, 500 / 140 = 3.6
    Saved = 3.6 (to 1 d.p)

    Hope this is ok.


  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,751
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    Hope this is ok.

    Not quite, Ben, at least for me.

    May I suggest that you select an empty cell, type = in that cell, select 10 rows of your data including the labels/column headings, hit F9, copy what you see, and paste it in the follow up, and tell explicitly where what must be computed?

    aladin





  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok, I'm assuming that your columns for 1st thru Saved are A thru L

    The formula for Saved (Column L) would be....
    =IF(G2="ANN",0,IF(AND(G2="KRO",F2="A"),ABS(I2-E2)*(K2/I2),IF(AND(G2="KRO",F2="B"),ABS(H2-E2)*(K2/H2),0)))

    The issue of "If under the header, Bar, the avlue "TO" is typed then the header Amt = Saved" could possibly create a circular reference with column L if you are not overwriting the formula to check if "BAR" = "TO" with a number......would have to see your data to understand that one. the formula for column K would be .... =IF(G2="TO",L2,0)

    Hope I understood it right......if not, yell in the next 2 hrs while I'm at work and I'll try again.

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Wow! Thanks Wi, thats great!

    Couple of more queries, can I get round the circular reference issue, i.e. have '=IF(G2="TO",L2,0)' in the same formula.

    Also, how can I make the formula apply to all of column L?

    Once again, thank you.

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry, Wi_Guy, one more thing:

    I add a new line between rows 4 and 5, so I get a new, empty, line 5, but the formula does not seem to recognise that line. Could you help on this, please?

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Posts
    160
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Wi_guy, looks like I've worked out the circular formula issue - thanks.

    But, still require help with applying the formula to all rows for column L.

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
  •  

 

 
DMCA.com