Posts by zohar

    sir, the total excel file is of 1.3 GB and I have tried using conditional formatting previously but it has slowed even the normal pageup/down thing tremendously. I am looking for logic of this , then I have to convert it to workable formulas in 14 files(each having 8 instances)

    HOPE

    Zohar Batterywala

    Respected Sir ,

    PRACTICAL: The issue is that when the levels in the upper reservoir is too much it is released in the lower reservoir , now normallly it is happening that this release is sometimes more than the capacity the lower reservoir can hold(and the structure is endangered).


    The solution I am trying: to monitor level of the reservoir so that difference(range between the opening and closing) is spotted.

    when the extra level is released from upper reservoir, a step-by-step movement has to be done so that immense flow does not come to lower reservoir which it can handle. So am trying to monitor (daily levels are updated every 4 hours , so 6 in a day). And similar step-by step can be released further.

    And the SECOND case is that if the increaseing levels is spotted in above feeder, lower feeder levels decreased pre-emptively to make way for the incoming future flow.


    OB-Overflow build up is spotted only after 3rd reading as it is normal that levels increase or decrease in normal flow. but 3rd consequence of it do trigger the situation. OB#, OB$, OB5, OB6 do mention that and when the OVERFLOW BUILDUP is released to lower reservoirs, it is flagger by OR

    ORA is flagging of abnormal levels seen(in lower reservoirs) if only one or two reading is too much fluctuating (due to OR from upper reservoir)


    Zohar

    Files

    • range.xlsx

      (13.23 kB, downloaded 16 times, last: )

    Respected,

    I am trying to formulate a way so that increasing/decreasing level in a reservoir can be known before hand so controlled flow can be assures.

    Zohar Batterywalarange.xlsx

    Respected Sir Glenn,

    Yes sir it was mistake by me

    Originally I had included the formula that was in D in the main formula in E

    The issue is solved at this time.

    Will request your help if I will need it.

    The issue with me is cant find a correct answer if has done a mistake once as that type of thought do continue when reviewing the problem. In that case others perspective make a big difference for me.

    THANK YOU SIR

    Zohar Batterywala

    Respected Sir,

    I had tried to use conditional formatting before but the plain file without any formatting is itself of 1.1 GB and takes 15-20 minutes for opening and 7-8 minutes to save and (conditional) formatting makes it more heavy.

    I am using This file just to calculate data (as 90% of formulas needs past data of atleast 15 days).

    Then this data is moved to an value only file (which is just becoming 200 MB).

    Zohar Batterywala

    Respected Sir Roy,

    This are the alerts

    Range is just for indication -it not to be included in formula

    High(A) Low(B) Last(C) Range alert/Mention(In E column)

    (1)8404 8390.15 8390.15 16.78 WLN▼ Last is vry low near low.Her▲also comes in range but last is low--needed to alert that level is too much low

    Row 3 in file

    (2)8410.35 8395.5 8410.35 16.82 WLN▲ Last is very high near high Here ▼ also comes in range but last is high--needed to alert that level is too much high

    Row 9 in file

    When Last reading is same as high or low Alert WLN▼(LOW) or WLN▲(HIGH) OR in 0.005%(ad not mentioned in file but I find this recently--Sorry for that)

    WLN=Warning Last Near

    -------------------------------------------------

    Normal Case

    High Low Last Range Mention(In E column)

    (3)8415 8385 8398.35 16.8 LN▼ here more near is low

    Row 5 in file

    (4)8415.35 8384.5 8400.35 16.8 LN▲ here more near is HIGH

    Row 7 in file

    (4)8441 8373.15 8400.35 16.8 OR none is in range-OUT OF RANGE

    Row 11 in file


    Originally in the sheet which I had to keep this formula here DATA is sergegared but will manage adjusting that in formula. This is the functionality scenario.

    THANK YOU

    Zohar Batterywala

    Respected Sir Roy,


    Needs HELP as the formula I made was not addressing all cases.


    Needs to make formula to alert/mention based on the level


    This is the same file that is attached in Initial post.


    Zohar

    Respected,

    Needs the data to be extracted from a sentence

    total is 5 data(so five formulas is needed)

    -1&2 are checksums so label is not given


    (1) column H

    (2) column I

    (3)nf% column J

    (4)2A column K

    (5)3A column L


    Please if possible mention me some note as how this formula(logic and how to use formulas that are used in that) as I might get more of this type of problem so can solve at my end.

    Zohar

    Previously I had a excel file of 500+ MB(has lot of formulas) and was creating memory problem, some suggested to remove formatting from it.
    Recently faced the same problem again and so removed >90% of formatting(borders, font style other than that of worksheet, fill color, Italics, bold)
    Kept only which are ver much needed.
    And then saved it .
    Previously it was of 527 MB , now it is of 533 MB .
    Also Memory usage previously was ~60%, now it it >70%
    Can you please suggest something
    Zohar Batterywala

    Respected Alan
    ,Gapup means the difference between close of last reading and opening of new reading (In my case its difference in level seen when the last reading is taken 2 hrs ago and now. so we can determine seepage)
    In calculated sheet the reading taken by system is in Row 9,12, 15, 18 .......
    The difference between last reading taken in previous interval is in B6,B9,B12,B15
    The reading taken at opening of interval is in A9, A12,A15, A15
    The difference between then B6 & A9 is in a10
    similarly
    difference between then B9 & A12 is in A13
    difference between then B12 & A15 is in A16
    difference between then B16 & A18 is in A19


    The repeatation of difference(+ve or -ve) is counted in B10, B13, B16
    Both of A10 & B10 data has to be included in A11 which also equalize
    % difference between last two reading,
    average of last 2 reading and
    average of last 3 reading
    PROBLEM---was able to include B10, B13, B16 calculation in A11,A14, A17
    but am NOT ABLE TO USE A10, A13, A16 data in A11,A14, A17
    as reference of functions in A11,A14, A17 is in A10, A13, A16
    and so give repetative loop error.
    THIS HAS TO BE SOLVED.


    --------------------------------repeating again-----
    Respected ,
    (1)Way
    in calculation of gapup and reoccurance,


    Is there anyway so that formulas done in a10,b10, A13,B13 & A16,b16) can be accomodated in A11, a14 & A17
    Results & formula in Row 10,13,16,.......... has to be maintained , as it is referenced by formulas in A11, a14 & A17 which complicates the structure.(has to insert extra row)


    (2) way
    There is the data that is obtained from the level mapping software that is in Row 9,12,15,18
    the calculations of gapup & reoccurance(of gapup) is calcuated in ROW a10,b10, a13,b13, a16,b13
    The other calculations based on it are in ROW a11,a14,a17
    I was able to display the calculated data of Row 10,13,16 in ROW 11,14,17
    but further down calculations can not be done as the data in ROW 10,13.16 are used as input in calculations of Row 11,13,16,17, and there it is mixed with other data , calculation in 11,14,17 is not done.
    I am looking the way so that the though the data of ROw 9 gets accommodated in Row 10, but calculation of 11,14,17, can be obtained,
    Same repeats further.
    Please
    (3)way
    To put all things again


    data in Row 9,12,15 & 18 is the data
    -opening(col A) & closing(col B) RAW reading of today


    data in Row 10,13,16 is the data that is obtained from formulas
    -COL A-gapup-down in % of todays opening compared to previous days close(for calculation Base is previous days closing)
    -COL B-reoccurance of thing gapup-down trend


    data in ROW 11,14,17,20
    difference of previous days; gapup-down-that is in a10,a13,a16, its 2-day and 3-average



    (what I need
    --at present to correctly use the gapup-down data(Row 10,13,16) , it has to be seperately plaaced,
    I want to merge it in calculated data in ROW 11,14,17,20


    (purpose-one row will be saved , where I can put other+weekly data)
    -----------------------------------------

    Respected,
    Had posted in two other groups but cant get the solution so posting here


    https://www.excelforum.com/exc…and-data.html#post5056937
    https://www.mrexcel.com/forum/…reoccurance+function+data



    Respected ,
    (1)Way
    in calculation of gapup and reoccurance,


    Is there anyway so that formulas done in a10,b10, A13,B13 & A16,b16) can be accomodated in A11, a14 & A17
    Results & formula in Row 10,13,16,.......... has to be maintained , as it is referenced by formulas in A11, a14 & A17 which complicates the structure.(has to insert extra row)


    (2) way
    There is the data that is obtained from the level mapping software that is in Row 9,12,15,18
    the calculations of gapup & reoccurance(of gapup) is calcuated in ROW a10,b10, a13,b13, a16,b13
    The other calculations based on it are in ROW a11,a14,a17
    I was able to display the calculated data of Row 10,13,16 in ROW 11,14,17
    but further down calculations can not be done as the data in ROW 10,13.16 are used as input in calculations of Row 11,13,16,17, and there it is mixed with other data , calculation in 11,14,17 is not done.
    I am looking the way so that the though the data of ROw 9 gets accommodated in Row 10, but calculation of 11,14,17, can be obtained,
    Same repeats further.
    Please
    (3)way
    To put all things again


    data in Row 9,12,15 & 18 is the data
    -opening(col A) & closing(col B) RAW reading of today


    data in Row 10,13,16 is the data that is obtained from formulas
    -COL A-gapup-down in % of todays opening compared to previous days close(for calculation Base is previous days closing)
    -COL B-reoccurance of thing gapup-down trend


    data in ROW 11,14,17,20
    difference of previous days; gapup-down-that is in a10,a13,a16, its 2-day and 3-average



    (what I need
    --at present to correctly use the gapup-down data(Row 10,13,16) , it has to be seperately plaaced,
    I want to merge it in calculated data in ROW 11,14,17,20


    (purpose-one row will be saved , where I can put other+weekly data)


    xls file with data is in dropbox
    https://www.dropbox.com/s/i0yd…xr8/forum%402feb.xls?dl=0


    PLEASE PLEASE help me
    Zohar Batterywala

    Respected,
    I think one of the issue is solved, now another issue in that same is to be solved.


    Also PLEASE PLEASE evaluate solution of the solved issue as it is working in all cases((1)-ve,-ve (2)-ve,+ve (3)Zero, -ve (4)-ve, zero, (5)zero,+ve and many other such ways


    And help me to find the solution of the next issue( values of R3 compared to values of R1)


    Zohar Batterywala [ATTACH]n1203448[/ATTACH]