Changing formula based upon word input

    Hi,

    My problem:

    Column (G) Column (H) Column (I) (THIS LINE AND LINE BELOW NEED TO BE PLACED ABOVE THE NUMBERS. THEY ARE IN CORRECT ORDER BUT I CANNOT MOVE THEM ALONG!)

    Stop Loss Take Profits Open Price [TABLE="width: 585"]

    [tr]


    [td]

    Row 1 Sell

    [/td]


    [TD="align: right"][/TD]
    [TD="align: right"]1.24765[/TD]
    [TD="align: right"]1.23853[/TD]
    [TD="align: right"]1.24423[/TD]

    [/tr]


    [tr]


    [td]

    Row 2 Buy

    [/td]


    [TD="align: right"][/TD]
    [TD="align: right"]79.482[/TD]
    [TD="align: right"]80.000[/TD]
    [TD="align: right"]79.741[/TD]

    [/tr]


    [/TABLE]



    What im trying to do is doing simple subtraction between the "OPEN PRICE - TAKE PROFITS" and then "OPEN PRICE - STOP LOSS". This is really straight forward, I know, BUT, when im doing the formula its shown as a minus number or positive because it isnt factoring in that one is a BUY price and the OTHER is a SELL price meaning the subtraction needs to be reversed on BUY prices.


    Below is an example just looking at "OPEN PRICE - TAKE PROFITS" BUT doesnt factor in that I also need the ANSWER to be expressed to different decimal places. Below will explain what it is I am looking for.



    =(I2-H2) = -0.259 = 25.9 POINTS
    =(I1-H1) = 0.00570 = 57.0 POINTS
    The outcome that SHOULD be read = 82.9 POINTS


    there are will only ever be two price formats to two different decimal places found within the table and i`ll try and show why I have the answer as 82.9


    00.000 (3 decimal places)
    0.00000 (5 decimal places)


    With this said, the numbers that are expressed in 3 decimal places (00.000) to me are are then broken down further and represented as POINTS. For example:


    00.000 + 00.555 = 55.5 POINTS
    00.000 + 01.555 = 155.5 POINTS
    00.000 + 10.555 = 1055.5 POINTS


    The number will never be expressed "000.000"


    The other decimal place number is to 5 decimal places, examples are seen below how the points are recorded:


    0.00000 + 0.00005 = 0.5 POINTS
    0.00000 + 0.00055 = 5.5 POINTS
    0.00000 + 0.00555 = 55.5 POINTS
    0.00000 + 0.05555 = 555.5 POINTS



    Everything above is simply looking at "OPEN PRICE - TAKE PROFITS" = expressed as points to relevant decimal places


    The rules above also need to be applied to "OPEN PRICE - STOP LOSS" = expressed as points to relevant decimal places




    I am looking for two formula basically and would be incredibly grateful for anyone's help!

    Re: Changing formula based upon word input


    I'm not sure what you're trying to do. If you want to change the formula based on whether it says 'buy' or 'sell' on a given line, that's an IF function. If you want to format cells with leading zeros, that's a formating thing. Can you provide a worksheet, with what you have now (exact structure, dummy data) and what your required output is?

    Re: Changing formula based upon word input


    Does this work?


    =ABS(F7-E7)*IF(C7="Sell",10000,100)


    copied down


    Format cells to Number with 1 decimal

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Changing formula based upon word input


    [QUOTE=NBVC;616854]Does this work?


    =ABS(F7-E7)*IF(C7="Sell",10000,100)


    copied down




    Man I am so thankful for your intelligence! That is awesome. With this formula, how do I then apply the rule (as I have a lot of data) that if its a BUY trade it will figure that out too?


    I.e. if you look at that spreadsheet earlier attached and apply your formula but to the trade "USDJPY" (as this figure is to 3 decimal places) - =ABS(F9-E9)*IF("Sell", 10000, 100) - it doesnt work? Even if I change the formula to "Buy" it doesn't work? (im not good with excel, sorry)


    Im wanting the formula to automatically determine in any given row "buy" and "sell" so that I can just apply the formula to a whole column full of rows and it gives me answer in one box?


    Thanks so much!

    Re: Changing formula based upon word input


    The formula I gave you assumes that if C7 is not "Sell", then it is "Buy" as you said there are only these 2 options


    =ABS(F7-E7)*IF(C7="Sell",10000,100)


    so first we subtract E7 from F7 and take the absolute value of that difference... then if C7 is a "Sell", we multiply the result by 10000, otherwise (it is assumed it is a "Buy" and therefore multiplies by 100... to give the results you desired.


    You copy the formula down, to get results for each row thereafter.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Changing formula based upon word input


    You sir, are a genius.... I am not. You have nailed it perfectly. Thank you very kindly indeed.


    One last question, is there any way (or is it too complicated?) to, rather than copy the formula down and get the results on every row; have it calculate all the data off of one column and at the bottom, beneath the "OPEN PRICE" calculate it all up and then find the average?


    :) or am I asking too much! No worries if not you`ve been a great help.

    Re: Changing formula based upon word input


    Try:


    =AVERAGE(ABS(F7:F9-E7:E9)*IF(C7:C9="Sell",10000,100))

    confirmed with CTRL+SHIFT+ENTER     not just ENTER. You should see { } brackets appear around the formula

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Changing formula based upon word input


    Forever grateful! Thank you very much for your time!


    REALLY appreciate your help :D worked like a charm!!! :thumbcoo:

    Re: Changing formula based upon word input


    Back! lol.


    Just a quick one, with that last formula you posted, how is it I add to the formula so it will take data from multiple tables?


    I.e.


    =AVERAGE(ABS(F7:F9,F16:F18-E7:E9,E16:E18)*IF(C7:C9,C16:C18="Sell",10000,100)) ?


    I appreciate the formula not being of this lay out, but hopefully you can see my thought process? Just simply wanting to add to this formula any column and row as I have multiple tables stacked beneath each other but with the exact same layout.... So its not one big table if that makes sense.

    Re: Changing formula based upon word input


    Need to learn more of the basics in Excel I guess!


    =AVERAGE(ABS(F7:F9:F16:F18-E7:E9:E16:E18)*IF(C7:C9:C16:C18="Sell",10000,100))



    When im applying this to a table where im expecting new data to be input throughout the week; I dont think the formula works properly? As some rows will have data and some will not and be blank until I put data in there? Its coming up with #Value! in the box where i`d expect the answer. (its nothing to do with ctrl, shift and enter as I am doing this)


    Basically i`d like to apply this (awesome) formula to multiple tables which are blank so that when I apply or upload data into the table it will automatically equate the answer....


    Any ideas?


    Also the average result isn't correct? I have attached excel spreadsheet to illustrate this very clearly! Should be fairly straight forward for people knowing excel to better depth than me! (which isnt hard!)

    Re: Changing formula based upon word input


    Hi again,


    In row 15, should that really be a "Buy" as you had indicated, the "Buy" are 3 decimals and "Sell" are 5 decimals numbers?


    Assuming it should be a "Buy", and assuming your tables are set up per your sample, below each other, perhaps separated by a couple of rows, then try:


    Code
    =AVERAGE(IF(ABS(F7:F15-E7:E15)>0,ABS(F7:F15-E7:E15)*IF(C7:C15="Sell",10000,100)))


    confirmed with CTRL+SHIFT+ENTER

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Changing formula based upon word input


    Ah my mistake! Ok, so basically, any "Trade" whether it has 3 or 5 decimal places can either be a "Buy" or a "Sell" - random.


    This includes the USDJPY... In the example I attached at the beginning, that particular "trade" happened to be a "Buy" and the 5 decimal place "Trades" happened to be a "Sell"


    I would need a formula to automatically take the information, irrelevant of 3-5 decimal places and if its a `buy` or `sell` `trade`



    Hope this makes sense?

    Re: Changing formula based upon word input


    I am not sure I understand, are the rules being changed? what do you mean by "any "Trade" whether it has 3 or 5 decimal places can either be a "Buy" or a "Sell" - random." Are you saying now that a Sell can be either 3 decimals or 5 decimals? If so, then the logic is out of sync.


    Does my formula work?

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Changing formula based upon word input


    Yes what you have written is correct^


    Sorry, my maths as im sure you can tell is weak so when trying to structure my goals logically can be difficult but the results I want is easier?



    I will paraphrase what you have written to make it as clear as possible:


    Whether its a 3 or 5 decimal place number they can either be "Buy" or "Sell" trades.




    (Bit of background)
    I am a foreign exchange trader wanting to see my statistics. I either BUY the market or SELL it.


    I only trade USD foreign exchange.


    EURUSD - Euro - Dollar - This (for example) along with any other market OTHER THAN USDJPY is 5 decimal places
    USDJPY - Dollar - Yen - trade-able to 3 decimal places (the only market I look at that trades this way)


    So a stab in the dark here, the formula may have to include USDJPY? As it will always be written that way, whether its a buy or sell. But once again this is the ONLY market on my data sheets that are to 3 decimal places.


    All can be either a buy or sell.....

    Re: Changing formula based upon word input


    Unfortunately, I am not a foreign exchange trader, so it is not as easy for me to understand.


    So are you now saying the formula should make an exception for any USDJPY in column B? That we should treat it whether it is a Buy or Sell, as a "Buy" (meaning multiply difference by 100, not 10000)

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login