Excel help

S

samadhan 123

New Member
Joined
Jun 11, 2023
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
i have following type of sheet I wrote formulas but when comes ABC i want to calculate same formula by considering just before trending high or trending low Highs and lows

sqlexcel.csv
ABCDEF
1DateOpenHighLowCloseCandleType
2Fri Jun 02 2023 09:15:00 GMT+0530 (India Standard Time)965.9969.65962.25962.65Trending Candle
3Fri Jun 02 2023 09:20:00 GMT+0530 (India Standard Time)962.65963.35961962.3Trending Low
4Fri Jun 02 2023 09:25:00 GMT+0530 (India Standard Time)962.3962.4958.5958.6Trending Low
5Fri Jun 02 2023 09:30:00 GMT+0530 (India Standard Time)958.95960.6958.4960.6Trending Low
6Fri Jun 02 2023 09:35:00 GMT+0530 (India Standard Time)960.6961.25959.25959.25Trending High
7Fri Jun 02 2023 09:40:00 GMT+0530 (India Standard Time)959.25960958958.5Trending Low
8Fri Jun 02 2023 09:45:00 GMT+0530 (India Standard Time)958.5959.05957.55958.2Trending Low
9Fri Jun 02 2023 09:50:00 GMT+0530 (India Standard Time)958.2959.65957.3957.65ABC
10Fri Jun 02 2023 09:55:00 GMT+0530 (India Standard Time)957.65958.35957.25958.2ABC
11Fri Jun 02 2023 10:00:00 GMT+0530 (India Standard Time)958.2958.3957.6958ABC
12Fri Jun 02 2023 10:05:00 GMT+0530 (India Standard Time)958.2958.25957957.25ABC
13Fri Jun 02 2023 10:10:00 GMT+0530 (India Standard Time)957.25957.25956.35957.2ABC
14Fri Jun 02 2023 10:15:00 GMT+0530 (India Standard Time)957.35957.85956.55957.65ABC
15Fri Jun 02 2023 10:20:00 GMT+0530 (India Standard Time)957.65957.65955.5955.7ABC
16Fri Jun 02 2023 10:25:00 GMT+0530 (India Standard Time)955.9956954.65955ABC
17Fri Jun 02 2023 10:30:00 GMT+0530 (India Standard Time)955956.9954.75956.9ABC
18Fri Jun 02 2023 10:35:00 GMT+0530 (India Standard Time)956.9957.75956.5957.65ABC
19Fri Jun 02 2023 10:40:00 GMT+0530 (India Standard Time)957.65958.65956.7957ABC
20Fri Jun 02 2023 10:45:00 GMT+0530 (India Standard Time)957958.45956.1957.95ABC
21Fri Jun 02 2023 10:50:00 GMT+0530 (India Standard Time)958958.5957.1958.45ABC
22Fri Jun 02 2023 10:55:00 GMT+0530 (India Standard Time)958.45960.4958960.2ABC
23Fri Jun 02 2023 11:00:00 GMT+0530 (India Standard Time)960.2960.2959.05959.4ABC
24Fri Jun 02 2023 11:05:00 GMT+0530 (India Standard Time)959.4959.85958.25958.7ABC
25Fri Jun 02 2023 11:10:00 GMT+0530 (India Standard Time)958.65959.1958.5958.95ABC
26Fri Jun 02 2023 11:15:00 GMT+0530 (India Standard Time)959.05959.9958.95959.35ABC
27Fri Jun 02 2023 11:20:00 GMT+0530 (India Standard Time)959.55960.2959960ABC
28Fri Jun 02 2023 11:25:00 GMT+0530 (India Standard Time)960960.8959.55960.25ABC
29Fri Jun 02 2023 11:30:00 GMT+0530 (India Standard Time)960.25961.55960.25961.45ABC
30Fri Jun 02 2023 11:35:00 GMT+0530 (India Standard Time)961.05961.45961961ABC
31Fri Jun 02 2023 11:40:00 GMT+0530 (India Standard Time)961.05961.45961961.15ABC
32Fri Jun 02 2023 11:45:00 GMT+0530 (India Standard Time)961.15961.25960.5961ABC
33Fri Jun 02 2023 11:50:00 GMT+0530 (India Standard Time)961961.15961961.15ABC
34Fri Jun 02 2023 11:55:00 GMT+0530 (India Standard Time)961.1963.1961962.25ABC
35Fri Jun 02 2023 12:00:00 GMT+0530 (India Standard Time)962.2962.25961.9962ABC
36Fri Jun 02 2023 12:05:00 GMT+0530 (India Standard Time)962962.7961.8962ABC
37Fri Jun 02 2023 12:10:00 GMT+0530 (India Standard Time)961.8962.7961.7962.15ABC
38Fri Jun 02 2023 12:15:00 GMT+0530 (India Standard Time)962.15962.5962962ABC
39Fri Jun 02 2023 12:20:00 GMT+0530 (India Standard Time)962.05962.25962962.2ABC
40Fri Jun 02 2023 12:25:00 GMT+0530 (India Standard Time)962.25963962962.65ABC
41Fri Jun 02 2023 12:30:00 GMT+0530 (India Standard Time)962.65963.8962.05962.2ABC
42Fri Jun 02 2023 12:35:00 GMT+0530 (India Standard Time)962.2962.75962.05962.05ABC
43Fri Jun 02 2023 12:40:00 GMT+0530 (India Standard Time)962.05962.45962.05962.2ABC
44Fri Jun 02 2023 12:45:00 GMT+0530 (India Standard Time)962.05963.2962.05962.95ABC
45Fri Jun 02 2023 12:50:00 GMT+0530 (India Standard Time)962.7963.25962.15962.6ABC
46Fri Jun 02 2023 12:55:00 GMT+0530 (India Standard Time)962.6962.9962.2962.2ABC
47Fri Jun 02 2023 13:00:00 GMT+0530 (India Standard Time)962.2962.45962962ABC
48Fri Jun 02 2023 13:05:00 GMT+0530 (India Standard Time)962962.2962962ABC
49Fri Jun 02 2023 13:10:00 GMT+0530 (India Standard Time)962962.4962962ABC
50Fri Jun 02 2023 13:15:00 GMT+0530 (India Standard Time)962962.25962962.05ABC
51Fri Jun 02 2023 13:20:00 GMT+0530 (India Standard Time)962962.2960.3960.3ABC
52Fri Jun 02 2023 13:25:00 GMT+0530 (India Standard Time)960.3961.65960.05961.3ABC
53Fri Jun 02 2023 13:30:00 GMT+0530 (India Standard Time)961.3962961.05961.95ABC
54Fri Jun 02 2023 13:35:00 GMT+0530 (India Standard Time)961.95962.6961.35962.3ABC
55Fri Jun 02 2023 13:40:00 GMT+0530 (India Standard Time)962.3962.8962962.35ABC
56Fri Jun 02 2023 13:45:00 GMT+0530 (India Standard Time)962.35962.6962962ABC
57Fri Jun 02 2023 13:50:00 GMT+0530 (India Standard Time)962962.5962962.05ABC
58Fri Jun 02 2023 13:55:00 GMT+0530 (India Standard Time)962.05962.5962962.05ABC
59Fri Jun 02 2023 14:00:00 GMT+0530 (India Standard Time)962.05962.8962962.2ABC
60Fri Jun 02 2023 14:05:00 GMT+0530 (India Standard Time)962.2963.55962.2963.15ABC
61Fri Jun 02 2023 14:10:00 GMT+0530 (India Standard Time)963.15964962.95963.9ABC
62Fri Jun 02 2023 14:15:00 GMT+0530 (India Standard Time)963.95964.5963.35964.2ABC
63Fri Jun 02 2023 14:20:00 GMT+0530 (India Standard Time)964.2964.5963964.35ABC
64Fri Jun 02 2023 14:25:00 GMT+0530 (India Standard Time)964.35964.5964964.05ABC
65Fri Jun 02 2023 14:30:00 GMT+0530 (India Standard Time)964.05964.1964964ABC
66Fri Jun 02 2023 14:35:00 GMT+0530 (India Standard Time)964964.7964964.25ABC
67Fri Jun 02 2023 14:40:00 GMT+0530 (India Standard Time)964.5964.8964964.65ABC
68Fri Jun 02 2023 14:45:00 GMT+0530 (India Standard Time)964.65965.85964.35965.6ABC
69Fri Jun 02 2023 14:50:00 GMT+0530 (India Standard Time)965.6965.6964.35964.95ABC
70Fri Jun 02 2023 14:55:00 GMT+0530 (India Standard Time)964.95965.5964.75965.25ABC
71Fri Jun 02 2023 15:00:00 GMT+0530 (India Standard Time)965.3966.6964.95966.6ABC
72Fri Jun 02 2023 15:05:00 GMT+0530 (India Standard Time)966.6967.2965.7965.9ABC
73Fri Jun 02 2023 15:10:00 GMT+0530 (India Standard Time)965.9966.75965.4966.1ABC
74Fri Jun 02 2023 15:15:00 GMT+0530 (India Standard Time)966.1966.1965.15965.85ABC
75Fri Jun 02 2023 15:20:00 GMT+0530 (India Standard Time)965.5966965.05965.75ABC
76Fri Jun 02 2023 15:25:00 GMT+0530 (India Standard Time)965.7967962.55965ABC
sqlexcel
Cell Formulas
RangeFormula
F3F3=IF(AND(F2="Trending Candle",C3>C2,D3>=D2),"Trending High",IF(AND(F2="Trending Candle",D3<D2,C3<=C2),"Trending Low",""))
F4:F76F4=IF(AND(OR(F3="Trending Low",F3="Trending High"),C4>C3,D4>=D3),"Trending High",IF(AND(OR(F3="Trending Low",F3="Trending High"),C4<=C3,D4<D3),"Trending Low","ABC"))
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
When there comes ABC and when we calculate trending high or trending low for next to ABC cell it will return ABC because there is ABC in previous cell.if such condition arises the high and low of nearest trending low or nearest trending high should used to calculate the formula
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Complexed Time Difference Formula
2
Replies
17
Views
1K
mahmed1
M
M
  • Solved
Formula Help.
Replies
3
Views
70
Fluff
Fluff
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
316
awoohaw
awoohaw
M
  • Question
suggest excel formula to calculate cumulative interest on no of days etc ......
Replies
1
Views
239
Peter_SSs
Peter_SSs
RichTom44
  • Question
How to highlight a certain time range
Replies
3
Views
306
SanjayGMusafir
SanjayGMusafir

Forum statistics

Threads
1,215,094
Messages
6,123,071
Members
449,092
Latest member
ipruravindra

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top