andrewmurray86
New Member
 Joined
 Jun 18, 2020
 Messages
 29
 Office Version

 2016
 Platform

 Windows
Hi team,
I'm wondering if there is a formula that might work like this VBA code? Formula to calculate percentage difference between peak and trough across a sequence of values?
I'd love to use it but have no idea how to get there, I cannot code and wouldn't know where to begin copying this code to make it work for my workbook.
Essentially I am hoping to find the difference between each new High and the following low point. I have a formula which has found where these points are (for the best part, it has identified once "false" valley, but only because I don't know how to tell it to ignore this) < Which if you have the knowledge to make it the formula ignore any doubles so the column reads only peak, valley, peak, valley etc rather than potentially valley, valley, peak, valley, peak, peak that would be great.
But, all that asideI'm struggling to get anything that might work calculating the difference between the highs and lows and I'm also struggling with identifying the cells which correspond to the peak and valley cells.
If that were possible then perhaps the formula could identify a peak then identify a valley, then calculate the difference between them, but give only the greatest value. And then the second greatest value.
In my spread sheet I have a value which is 8.12% below start value. Then the next value will be the difference between cells S66 and S95, which is easily calculable however once the spreadsheet has over 1000 entries finding these values will be the hard part.
I'm wondering if there is a formula that might work like this VBA code? Formula to calculate percentage difference between peak and trough across a sequence of values?
I'd love to use it but have no idea how to get there, I cannot code and wouldn't know where to begin copying this code to make it work for my workbook.
Essentially I am hoping to find the difference between each new High and the following low point. I have a formula which has found where these points are (for the best part, it has identified once "false" valley, but only because I don't know how to tell it to ignore this) < Which if you have the knowledge to make it the formula ignore any doubles so the column reads only peak, valley, peak, valley etc rather than potentially valley, valley, peak, valley, peak, peak that would be great.
But, all that asideI'm struggling to get anything that might work calculating the difference between the highs and lows and I'm also struggling with identifying the cells which correspond to the peak and valley cells.
If that were possible then perhaps the formula could identify a peak then identify a valley, then calculate the difference between them, but give only the greatest value. And then the second greatest value.
In my spread sheet I have a value which is 8.12% below start value. Then the next value will be the difference between cells S66 and S95, which is easily calculable however once the spreadsheet has over 1000 entries finding these values will be the hard part.
Cell Formulas  

Range  Formula  
P2:P98  P2  =IF(#REF!="balance",0,#REF!) 
Q2  Q2  =#REF! 
R2  R2  =W1+N2 
S2:S98  S2  =IF(AND(R1>=R2,R3>=R2,R4>=R3),IF(R2<MIN($R$2:$R$252)/0.95,"Valley",""),IF(AND(R1<R2,R3<R2),IF(R2>MAX($R$2:$R$252)*0.998,"Peak",""),"")) 
Q3:Q98  Q3  =IF((AND(P3<0,Q2<=0)),P3+Q2,IF((AND(P3>0,Q2>=0)),P3+Q2,0)) 
R3:R98  R3  =R2+N3 
Cells with Conditional Formatting  

Cell  Condition  Cell Format  Stop If True  
S2:S200  Cell  contains a blank value  text  NO 
S2:S200  Cell  contains a blank value  text  NO 
P2:Q75,Q3:Q200  Cell Value  <0  text  NO 
P2:Q75,Q3:Q200  Cell Value  >0  text  NO 
O2:S2,A96:R200,O3:R95  Cell  contains a blank value  text  NO 
O2:S2,A96:R200,O3:R95  Cell  contains a blank value  text  NO 
Last edited: