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:
