to Eliminate row needed for gapup and reoccurance function and data

I'm lost ZOHAR.

The words don't match the workbook you attached.

Regards,

David

Respected David,
New title made as per forum rules
to Eliminate row needed for gapup and reoccurance function and data
I am very sorry but after I wrote the note I add extra ROW to insert title at top (in excel sheet) so the problem has occured .I am SORRY

formula in Row 9,12,15,.......... has to be maintained , as it is referenced by formulas in A10, a13 which complicates the structure.(has to insert extra row)
Is there anyway so that value reference(FOR formula in A16 -------data and formula that are in a9, a12, a15 &b12) can be accomodated in A10, & A13
Is there anyway so that value reference(FOR formula in A19 -------data and formula that are in a12, a15, a18 &b15) can be accomodated in A13, & A16,
(we cant use A16 to move data that is in A15 as the formula will not be calculated
Similarly (we cant use A19 to move data that is in A18 as the formula will not be calculated
Zohar

Respected Madam,
changed to
to Eliminate row needed for gapup and reoccurance function and data

REspected Madam,
There is the data that is obtained from the level mapping software that is in Row 8,11,14,17
the calculations of gapup & reoccurance(of gapup) is calcuated in ROW 9,12,15,18
The other calculations based on it are in ROW 10,13,16,19
I was able to display the calculated data of Row 9,12,15,18 in ROW 10,13,16,19
but further down calculations can not be done as the data in ROW 9 & 10 are used as input in calculations of Row 13,16, and there it is mixed with other data , calculation in 15,18 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 13,16, can be done,
Same repearts further.
Please
Zohar

Sorry ZOHAR. I'm still lost. I'm just a broken down old accountant, so I have no idea what you are trying to achieve.

I get most of what you said to Ali, but the last bit ....

I am looking the way so that the though the data of ROw 9 gets accommodated in Row 10, but calculation of 13,16, can be done,
Same repearts further.
Please
Zohar

... I couldn't work out what you wanted.

Sorry I can't help yet.

David

Respected Alan,
I am very sorry, but I posted in that forum as The Query is not even viewed by others,
I AM REALLY SORRY
the links are
(1)https://www.mrexcel.com/forum/excel-...+function+data
(2)https://www.ozgrid.com/forum/forum/h...ction-and-data

I AM REALLY SORRY

Zohar Batterywala

PLEASE PLEASE help me
Zohar Batterywala

Sorry ZOHAR,

Your post is still very confusing as you are not telling us what you expect to see on rows 11, 14, 17 and 20.

These cells are just TEXT strings, so if you are wanting to add the contents of other cells, all you need to do is use the ampersand (&) followed by the cell reference at the end of the very complex formula you have uses to build your answer.

Can't offer any more without seeing what you need to see -

Respected David Sir
(1)Way

in calculation of gap-up and reoccurance,

Results & formula in Row 10,13,16,.......... has to maintain,as it is referenced by formulas in A11, a14 & A17 which complicates the structure.(has to insert extra row)

Is there anyway so that formulas done in a10,b10, A13,B13 & A16,b16) can be accommodated in A11, a14 & A17(nothing is needed in B11,B14,B17,.......)
Formula in A11,A14,A17 is


=IF(AND(B16<>"",B16>0,A19>A16),B16+1,IF(AND(B16<0,A19<A16),B16-1,IF(AND(A19>A16,A16>A13),2,IF(AND(A19<A16,A16<A13),-2,""))))&"EG("&IFERROR(ROUND(ABS(SUBSTITUTE(A19,"#","")*100/SUBSTITUTE(A16,"#","")-100),2)*-1^(A16>A19),"N/A")&" %2D(2A|"&IFERROR(ROUND(AVERAGE(--SUBSTITUTE(A19,"#",""),--SUBSTITUTE(A16,"#","")),2),"N/A")&"|3A|"&IFERROR(ROUND(AVERAGE(--SUBSTITUTE(A13,"#",""),--SUBSTITUTE(A16,"#",""),--SUBSTITUTE(A19,"#","")),2),"N/A")

(I am able to find it as formula in xls file at my end that is attached)
(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 gap-up & re-occurrence(of gap-up) is calculated 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-gap-up-down in % of today's opening compared to previous days close(for calculation Base is previous days closing)
-COL B-re-occurrence of gap-up-uptrend/down trend(repeatation of difference(+ve or -ve)

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


(what I need
--at present to correctly use the gap-up-down data(Row 10,13,16) , it has to be separately placed,

------gap-up explanation in a different way-----
Gap-up 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 repeatative loop error.
THIS HAS TO BE SOLVED.

Sorry ZOHAR (again).

I just don't get it because you still haven't said what you want to see.

A11: -2EG(-621.05 %2D(2A|-1.56|3A|965.29 - this is a text string created by a formula of more that 400 characters. That string means something to you and your colleagues, but nothing to me.

What should be the results in A11, A14 and A17? Perhaps if I knew that, I would have a chance of solving your problem.

David

Respected Sir,
Here in -2EG(-621.05 %2D(2A|-1.56|3A|-1.14

(1)-2 -- The data is b6 is 2905 and in b9 is 2838.7 so from 2 times the closing level is continuously going down(and that is same in B10)
(2)-621.05 %2D-difference betweenA6(2897 -reading 2 ) & A3(2899-reading 1 is -0.38
and difference between A9(2825.5 -reading 3 ) & A6(2897-reading is -2.74
This is % difference between these two readings
(3)2 day average of -0.38 and -2.74 is -1.56
(4)2 day average of -0.31, -0.38 and -2.74 is -1.56 is -1.14

A11 should also include figures of a10 so will be
-2.74#-2EG(-621.05 %2D(2A|-1.56|3A|-1.14
A14 should also include figures of a13 so will be
-4.4#-3EG(-60.58 %2D(2A|-3.57|3A|-2.51
A17 should also include figures of a16 so will be
0.51#EG(111.59 %2D(2A|-1.95|3A|-2.21

The problem I face is data of A10 is further needed for calculating results of B10 & A11
Similarly data of a13 is further needed for calculating results of B13 & A14
Similarly data of a16 is further needed for calculating results of B16 & A17
and so merging values of A10 with A11 will not enable to run B13 & A14
Simrly on so merging values of A13 with A14 will not enable to run B16 & A17
If that can be done that empty space(Row 10, 13 &15 can be used for other important data
Zohar Batterywala

HI,

You've confused me again with A11.

In your worksheet it calculates to -2EG(-621.05 %2D(2A|-1.56|3A|965.29 - but you have ended it with -1.14 (instead of 965.29)

That said, to get your answer, all you have to do is concatenate cell A10 (A13 and A16) to the front of your formula as I said in my post #12. Did you try it?

At the beginning of your huge formula, just add A10&"#"& (or A13 etc) so instead of =IF(AND(B7<>"" ..... you will have =A10&"#"&
IF(AND(B7<>""...

The & operator is just a shorthand way of using the CONCATENATE() function.

I hope this helps, please let me know!

Regards,

David

If this has been helpful
- Please click on the *Add Reputation button at the bottom left.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Respected David Sir ,
I do not know how that how 965.29 was calculated , but originally that is the 3 day average of difference of 3 reading(-0.31 in a4, -0.38 in a7 & -2.74 in a10,
Zohar Batterywala

I see the problem - the formula in A11 was calculating the average using A3 instead of A4. Once I fixed that, I get the answer you're looking for.

Have we solved the problem yet?

If so, please let me know, and please show your appreciation bu clicking the * Add Reputation button.

Regards,

David