need help-increasing/decreasing

[ZOHAR]

Respected,
The display is the answer that I need to get from formula.(means just one numeral with plus or minus sign Meaning is just added to give somewhat of description.

cell   #values	#display   #meaning
b2     # 1.4			
b3     # 1.5			
b4     # 1.7	# +2       #increasing in last 2 days	
b5     # 1.8	# +3	  #increasing in last 3 days	
b6     # 1.3			
b7     # 1.2	# -2	  #decreasing in last 2 days	
b8     # 1.1	# -3	  #decreasing in last 3 days	
b9     #-0.8			
b10    #-0.6			
b11    #-0.4	#  +2	  #increasing in last 2 days	
b12    #-0.3	#  +3	  #increasing in last 3 days	
b13    #-0.6			
b14    #-0.7	# -2	  #decreasing in last 2 days	
b15    #-0.8	# -3	  #decreasing in last 3 days

Zohar

[davesexcel]

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and click "manage attachments" to open the upload window.




To add a file to a post

[Kaper]

Leave c2 empty.
In c3 write formula:

=IF(AND(C2<>"",C2>0,B3>B2),C2+1,IF(AND(C2<0,B3<B2),C2-1,IF(AND(B3>B2,B2>B1),2,IF(AND(B3<B2,B2<B1),-2,""))))

and copy it down.

Note that in C9 you will get 4, beacuse B9 (-0,8) is fourth decreasing value in a row

[ZOHAR]

Respected KAPER,
THANK YOU VERY VERY VERY MUCH
Zohar Batterywala

[ZOHAR]

Respected,
Originally the completed field is having the RAW data from other instrumentation of daily readings of level.
the format is (+x.x(+x.x#+x.x)) And + shows that the reading is above Zero. If it is below zero , it is "-"(-x.x(-x.x#-x.x)).
when that formula was inserted in the data that is feeded in the sheet . output is not consistent.

Am attaching the completed sheet with the data on which I had applied the solution.
Zohar Batterywala

[ZOHAR]

Respected,
Originally the completed field is having the RAW data from other instrumentation of daily readings of level.
the format is (+x.x(+x.x#+x.x)) And + shows that the reading is above Zero. If it is below zero , it is "-"(-x.x(-x.x#-x.x)).
when that formula was inserted in the data that is feeded in the sheet . output is not consistent.

Am attaching the completed sheet with the data on which I had applied the solution.
Am posting again as I cant find the attachment.

Zohar Batterywala

[Kaper]

As you can see, your column F is left-aligned. It is a clear message from Excel, that you got texts there.
While my formula expects numbers as input.
So in F2 (and copy down) write not just Left function, but:

=VALUE(LEFT(E2,4))

Notice that column F is right-aligned now, as there are numbers, not texts, which just look like a number.

Best,

[ZOHAR]

Respected Sir Kaper,
yes , worked.
Thank you VERY MUCH

Zohar Batterywala

[ZOHAR]

Respected Sir Kaper,

Has changed the formula in all that to make its work with you suggested solution(value(LEFT)). (column E,F,G)
Need some way to make it such that if some data has not +sign in front of positive number, then also the function will execute.(column H,I,J)
I think there will be no issue with negative numbers(-)

Zohar Batterywala

[Kaper]

Do not focus on leftmost 4 characters. Instead, look for the position od percent sign, like J2:

=VALUE(LEFT(H2,FIND("%",H2)))

By the way, it reminds me the famous GIGO rule - If you do not show representative input, do not expect proper output

[ZOHAR]

Respected Sir Kaper,
you are right in your say that I did not mentioned correct input but as the data on which I am working is somewhat sensitive, so I am not allowed to share exact data.
Sorry for that
Zohar Batterywala

[ZOHAR]

Respected Sir ,
I have applied this formula to other cells but it is not getting the the desired output.
I am sure that I am not of your (expert) level so am not able to tweak the formula accordingly.
Please tweak the formula and the best to the original value is in E , so I need proper output in Column F

[JeteMc]

If I understand correctly then the following formula will populate column F with the values to the left of the open parentheses mark in the cells in column E:

=VALUE(LEFT(E2,FIND("(",E2)-1))

Let us know if you have any questions.

[ZOHAR]

Respected JeteMc,
Yes it is orking.
I was not well , so didnt log in for two days
Zohar Batterywala

[JeteMc]

Glad that the formula works and that you are now feeling better. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

[ZOHAR]

Respected JeteMc,
Last Help need in solving this issue.
I had applied the formula and it is working in columnD(D22,D25,D28...) ,
Now as there is space constraint, so has to merge it with column E(E22,E25, E28,......,
All other is working but its not working when the result is +ve (eg E28)

Increasing/decreasing count can be at fromt or back so I can call it in another cell using LEFT, or RIGHT

The functionality is needed in column E,F,G,H,L & O

Zohar Batterywala

[JeteMc]

...All other is working but its not working when the result is +ve (eg E28)...

I need to know what should be displayed in cell E28 and/or an explanation of why what is now displayed is incorrect.
Let us know if you have any questions.

[ZOHAR]

Respected JeteMC.
The output in D28 is as per the formulas given by you on saturday after doing needed modifications and that is correct=VALUE(LEFT(E2,FIND("(",E2)-1))
and its correct as E21= -1.49 , E24= 0.29 & E72= 0.72 , so its increasing , so the output 2 is correct and that should be same in first digit in E28 but it shows 0
Zohar Batterywala

[JeteMc]

The formula in E28 begins: =IF(AND(LEFT(E25,2)<>"",LEFT(E25,2)>0,E27>D24),LEFT(E25,2)+1...
LEFT(E25,2) is -1 and -1+1=0
It would seem that if the values in column D are correct as is the case with D28, then it seems the formula in E28 could read: =IF(AND(LEFT(E25,2)<>"",LEFT(E25,2)>0,E27>D24),D28...
Let us know if you have any questions.

[ZOHAR]

Respected JeteMC,
Please mention the completed formula as it is not easy for me to find error if it occurs.
It is okay for me to change the cell references.
But please construct atleast one formula AND I can create other based on that
Zohar Batterywala

[JeteMc]

Try the following in E28:

=IF(AND(LEFT(E25,2)<>"",LEFT(E25,2)>0,E27>D24),D28,IF(AND(LEFT(E25,2)<0,E27<D24),LEFT(E25,2)-1,IF(AND(E27>D24,D24>E21),2,IF(AND(E27<D24,D24<E21),-2,""))))&"#EG#"&IFERROR(ROUND(ABS(SUBSTITUTE(E27,"#","")*100/SUBSTITUTE(D24,"#","")-100),2)*-1^(D24>E27),"N/A")&" %2D (2A#"&IFERROR(ROUND(AVERAGE(--SUBSTITUTE(E27,"#",""),--SUBSTITUTE(D24,"#","")),2),"N/A")&"#3A#"&IFERROR(ROUND(AVERAGE(--SUBSTITUTE(E21,"#",""),--SUBSTITUTE(D24,"#",""),--SUBSTITUTE(E27,"#","")),2),"N/A")

Let us know if you have any questions.