Using MID formula to find values within text

  • Respected,
    4
    -202.15
    6.9
    -17.9
    13
    11forum.ozgrid.com/index.php?attachment/70102/



    -15.38 (2.33#-0.35) 2.33 -0.35
    Problem(1)
    AVERAGE -0.35 should come -30~ but as mid is only considering first 3 , while A2(-202) has 4
    Problem(2)
    mid function IS USED AS ONLY RELEVANT NUMBERS FROM A STRING HAS TO BE CONSIDERED
    BUT THIS CREATED PROBLEM WHEN THE NUMBER OF NUMERALS TO BE CONSIDERED ARE NOT ENOUGH AND HAS OTHER CHARACTER/SYMBOLS BEHIND THE SIGN(WHICH IS NORMAL)
    In future the case can come where there may be character/symbols ahead of it and delimiter used is # , so if possible make that version also
    So has to make formula such that only numerals that are at starting of value is considered(till 1 places after decimal, if there is decimal)


    Zohar

  • Re: MID function error


    Respected Sir,
    After I posted here I waited for 4-5 days to get a answer from this forum, and then post in another group.(previously I was intimated for that reason only BUT at that time I was new so was not aware of this).
    I do have working formula for this but my formula make an error in counting so have asked this. This is important for me. I have to further apply it to 1000+ places and also make needed adjustments for that.


    Please do help me.PLEASE PLEASE
    Zohar Batterywala

  • Re: MID function error


    Respected
    ,

    The format of the file in which this has to be added(to calculate) is:


    Every day at end of day, the date of that day is added


    say the date is 1-sep on A1


    after that one line(ROW) is left blank(as other data that is added is of 2 lines(ROWS) and one line is needed for calculation)


    a week can have 4 or 5 days(A1-A10 or A1 to A13) and so difference has to be maintained.
    pictorially-
    A1(DATA OF 1-SEP)
    A2(blank)
    A3(calculated data of 1-sep)
    A4(DATA OF 2-SEP)
    A5(blank)
    A6(calculated data of 2-sep)
    A7(DATA OF 3-SEP)
    A8(blank)
    A9(calculated data of 3-sep)
    A10(DATA OF 4-SEP)
    A11(blank)
    A12(calculated data of 4-sep)
    A13(DATA OF 5-SEP)
    A14(blank)
    A15(CALCULATED DATA OF WEEK)
    Now the data of A3-
    FOR WEEKLY-Now the data that is only on A3-A6-A9-A12 is only to be considered ( I will manage all that is not as per routine ---- like 4 days week, etc. etc.) .
    THE DATA ON A1-A4-A7-A10(AND A13) HAS NOT TO BE CONSIDERED.
    Hope I can make the things clear. If not you can message me anytime for anything(my email ID is---- zoharsb at gmail dot com)



    -----example
    A1--- 4
    A2--- -202.15
    A3--- 6.9
    A4--- -17.9
    A2--- 13
    a6-- 11
    AVG- -15.38 (2.33#-0.35)

    Problem(1)

    AVERAGE -0.35 should come -30~ but as mid is only considering first 3 , while A2(-202) has 4
    Problem(2)
    mid function IS USED AS ONLY RELEVANT NUMBERS FROM A STRING HAS TO BE CONSIDERED
    BUT THIS CREATED PROBLEM WHEN THE NUMBER OF NUMERALS TO BE CONSIDERED ARE NOT ENOUGH AND HAS OTHER CHARACTER/SYMBOLS BEHIND THE SIGN(WHICH IS NORMAL)
    In future the case can come where there may be character/symbols ahead of it and delimiter used is # , so if possible make that version also
    So has to make formula such that only numerals that are at starting of value is considered(till 1 places after decimal, if there is decimal)


    Zohar Batterywala

  • Re: MID function error


    Hi,


    I'm afraid that both your explanations and your examples are unclear.


    Suggest you upload a new workbook with several varied examples and, importantly, your expected results.


    Regards

  • Re: MID function error


    I'm afraid that, at least for me, that new version of the file adds nothing in terms of clarity to your previous uploaded files.


    I would suggest pasting three or four columns' worth of data into a post and giving the expected result for each.


    Regards

  • Re: MID function error


    Respected,
    forum.ozgrid.com/index.php?attachment/70332/
    mid function IS USED AS ONLY RELEVANT NUMBERS FROM A STRING HAS TO BE CONSIDERED
    BUT THIS CREATED PROBLEM WHEN THE NUMBER OF NUMERALS TO BE CONSIDERED ARE NOT ENOUGH AND HAS OTHER CHARACTER/SYMBOLS ahead/BEHIND THE SIGN--if is -ve(WHICH IS NORMAL)
    seen in e57,e60, e63



    So has to make formula such that only numerals string(with -ve sign if is there) that are at starting of value is considered(till 1 places after decimal, if there is decimal)----
    after "#" delimiter till the next "#" delimiter.





    Zohar Batterywala

  • Re: MID function error


    Sorry, but I'm afraid that both your attachment and attempted explanation do not differ from the previous ones you gave, which, as I've already stated, I was not able to understand.


    Perhaps we should try another way:


    1) Paste 3 or 4 examples into a post (NO attachments)
    2) Give your expected results for each
    3) DO NOT show me your current formula(s)


    Regards

  • Re: MID function error


    Unfortunately I didn't ask for an updated attachment. And you still haven't made it clear which are the expected results.


    As such, I'm afraid I'll have to pass on this thread now. Hopefully somebody else will read it who can help you.


    Regards

  • Re: MID function error


    Respected SIr,


    C63 in the attached file is the required result-it is


    (1)% wise difference between last 2 days
    (2)2Day average of the original value (data feed done-NOT calculated figure)
    (3)3Day average of the original value (data feed done-NOT calculated figure)
    (4)weekly average of the original value (data feed done-NOT calculated figure)


    All four results on in same cell connected by &



    Sir, Please Please understand that I am not 24X7 online but am able to check mail at every 2-3 hours and so replied immediately as soon as you asked. PLEASE PLEASE help me to get the solution as this will solve many of my issues(relating to inflow from dam-to-reservoir, reservoir-to canal, and many,
    there are 7 calculation in each line and there are such 4dams*13reservoirs*15canals=780* 360 days -280,800 lines,


    If I have to change MID values in each, it will take me a month or so, and even that if done will not solve the problem as the persons who will be really using night not have required know-how.


    PLEASE PLEASE


    Zohar Batterywala

  • Re: MID function error


    Thanks.


    So, for the formula in cell C63:


    Quote from zohar;778371

    (1)% wise difference between last 2 days


    In which cells are the last 2 days' figures in this case? C58 and C61? Can these be entered as static references within the formula, or do they need determining dynamically?


    Quote from zohar;778371

    (2)2Day average of the original value (data feed done-NOT calculated figure)


    What do you mean by "2Day average"? Which values should be considered? In which cell is the "original value"?


    Quote from zohar;778371

    (3)3Day average of the original value (data feed done-NOT calculated figure)


    What do you mean by "3Day average"? Which values should be considered?


    Quote from zohar;778371

    (4)weekly average of the original value (data feed done-NOT calculated figure)


    What do you mean by "weekly average"? Which values should be considered?


    Regards

Participate now!

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