Динамическая формула, которая вычитает 2 значения ячейки на основе значений из 2 других ячеек

Я изо всех сил пытаюсь найти титул на этот вопрос, поскольку я не могу понять, как лучше всего это выразить. Я считаю, что с моими фотографиями я смогу объяснить свой вопрос лучше.

Это также может быть слишком требовательным к формуле и нуждается в кодировании VBA

Я хотел бы создать формулу, которая анализирует диапазон из двух столбцов значений. Первый столбец – скорректированная цена закрытия акции. Второй столбец представляет собой вычисляемый осциллятор, предназначенный для прогнозирования состояния сверхпрочности.

В этой формуле запасы считаются недокументированными, если расчетный осциллятор ниже 30 и перекуплен, если он выше 80. Поэтому я хотел бы рассчитать разницу ценовых цен закрытия между первым вхождением в подкуп и последующим экземпляром перекупленности.

Затем я хочу, чтобы эта формула охватывала все экземпляры цены акций за год. Теоретически будет много раз, когда запасы будут поглощены и перекуплены.

Я думаю об этих диапазонах в периоды по существу. Начало периода – это первый экземпляр числа подкуплений, следующего за последним экземпляром значения перекупленности. Конец периода – следующее значение перекупленности.

Количество дней, когда запасы подкуплены / перекуплены, будет варьироваться оттого, и поэтому это должна быть динамическая формула.

Ive рассмотрел использование вложенных операторов if и select, но я не могу придумать что-то достаточно прочное. Может ли кто-нибудь указать мне в правильном направлении, какие функции использовать для этого? Это что-то, что потребует VBA?

Пример того, что я хотел бы сделать

введите описание изображения здесь

Спасибо, что нашли время, чтобы прочитать и подумать об этом. Пожалуйста, дайте мне знать, если я смогу прояснить что-нибудь для вас.

Вам нужно настроить небольшой конечный автомат для такого рода проблем, и не имеет значения, используете ли вы VBA или вспомогательные столбцы.

Итак, есть три состояния

(1) Начальное состояние

(2) Состояние «Грунтовка»

(3) В состоянии периода

и три перехода

(A) От (1) до (2) при наличии запасов перекупленности

(B) Из (2) – (3), когда найден следующий запас

(C) Из (3) назад к (1), когда найден следующий запас перекупленности.

Поэтому вам нужно настроить столбец для обработки этих состояний и переходов, содержащих формулу

=IF(AND(I2=1,H3>80),2,IF(AND(I2=2,H3<30),3,IF(AND(I2=3,H3>80),1,I2))) 

начиная с (скажем) I3 (I2 устанавливается в 1).

Затем вам нужно еще два вспомогательных столбца, чтобы подсчитать окончания периода и начать

 =IF(AND(I1=2,I2=3),MAX(J$1:J1)+1,"") =IF(AND(I1=3,I2=1),MAX(K$1:K1)+1,"") 

И, наконец, колонка, чтобы понять различия

 =IFERROR(INDEX(G$2:G$100,MATCH(ROWS(L$1:L1),J$2:J$100,0))-INDEX(G$2:G$100,MATCH(ROWS(L$1:L1),K$2:K$100,0)),"") 

введите описание изображения здесь

VBA, чтобы найти разницу для n-го периода, будет выглядеть ниже: вы бы назвали это так, начиная с (скажем) I2

 =IFERROR(stock(G$2:G$100,H$2:H$100,ROWS(N$1:N1)),"") 

так как вы вытаскиваете формулу вниз, она будет искать следующий период.

 Option Explicit Function Stock(Actual As Range, Predicted As Range, n As Integer) As Variant Dim i, rows, LastRow, period As Long Dim state As Integer Dim startFound, endFound As Boolean Dim actualValue, predictedValue, startValue, endValue As Double state = 1 period = 0 startFound = False endFound = False rows = Actual.rows.Count If rows <> Predicted.rows.Count Then Stock = CVErr(xlErrValue) Return End If For i = 1 To rows actualValue = Actual.Cells(i, 1).Value predictedValue = Predicted.Cells(i, 1).Value If state = 1 And predictedValue > 80 Then state = 2 Else If state = 2 And predictedValue < 30 Then state = 3 period = period + 1 If period = n Then startValue = actualValue startFound = True End If Else If state = 3 And predictedValue > 80 Then state = 1 If period = n Then endValue = actualValue endFound = True Exit For End If End If End If End If Next I If startFound And endFound Then Stock = startValue - endValue Else Stock = CVErr(xlErrValue) End If End Function 
Interesting Posts

тот же код vba приводит к генерированию разных графиков

Использование таблицы: SimpleExcel

Как прокручивать столбцы A и Paste, когда колонка A выделена жирным шрифтом

Удалите символ в excel до первого символа

Сохранение рабочей книги на Python с использованием xlwt дает ошибку

Ошибка чтения больших файлов Microsoft Excel с использованием XLSREAD в MATLAB 7.0.1 (R14SP1)

Как вывести знак евро с таблицей :: WriteExcel из столбца nvarchar сервера MSSQL

Excel разбивает столбец на несколько столбцов без разделителя

Копировать дату из Excel в Word в другом формате

Преобразование MATLAB: CELL to DOUBLE; Но цифры меняются

Самый быстрый способ загрузить файл .xlsx в базу данных MySQL

используя excel vba, чтобы изменить значение выпадающего меню, которое после активации и заполнения второго выпадающего меню

INDEX MATCH с несколькими поисковыми системами

Java записывает файлы Excel с помощью модели событий POI

просмотреть файл XLSX в браузере C #

Давайте будем гением компьютера.