Welcome Guest.


Vba :a column date, bc column is the value, column b minus c column, according to the period of f2, g2, the maximum and minimum values ​​are displayed in f3 and g3 respectively.

Asked by: Linda Watson 225 views IT January 24, 2019

          A            B        C               F                     G

2 20180201   2000  1000      20180201  ---  20180207     Time period is your own input

3  20180201   1500   1000        2500                600            Show maximum and minimum values ​​at f3, g3               

4  20180202   2500   1500

5 20180203   2000   4000

6 20180203   2000   1900

7 20180204   2000   1000

8  ,

n    20181220   2000   1000

There are multiple rows of data in a day, so the maximum and minimum values ​​are calculated by date. The third row of the above data is the maximum value.

The fifth row is the minimum value of 500, but the sixth row value is 600. Date, so the minimum value should display 600

Addition: Expert help

Addition: To display the maximum value and minimum value accumulated by date

Addition : I added a screenshot, please help me! ! !

1 Answers

  1. +5Votes  

    The fifth line is how the minimum value is 500?

    The sixth line is the minimum 600?

    Please say it again

    Question: 2500 is the sum of d2 to d4,
    The fifth and sixth lines are like this.
    The fifth and sixth lines are the same day, so the value of the sixth line is displayed.

    Question: My description is not very clear. For example, my total income is 2500 from 1st to 2nd, and the income in the morning of the 3rd is -2000, and the income in the evening is 100, so 3rd. The total income is 2500-200+100=600,

    Answer: Is this the case?

    Answer: The formula is shown in the picture below (1)

    Answer: The formula is shown in the picture below (2)

    Answer: =SUM($B$2:B2, -$C$2:C2)
    =IF(MAX(IFERROR((A2:A7>=F2)*(A2:A7<=G2)*E2:E7,MIN(E:E)))),MIN(IFERROR((A2:A7>= F2)*(A2:A7<=G2)*E2:E7,MAX(E:E))),MAX(IFERROR((A2:A7>=F2)*(A2:A7<=G2)*E2:E7 ,MIN(E:E))))
    =IF(MIN(IFERROR((A2:A7>=F2)*(A2:A7<=G2)*E2:E7,MAX(E:E)))),MAX(IFERROR((A2:A7>= F2)*(A2:A7<=G2)*E2:E7,MIN(E:E))),MIN(IFERROR((A2:A7>=F2)*(A2:A7<=G2)*E2:E7 , MAX(E:E))))

    Question: I added the screenshot, the display is 500 when g3=min(d2:d8), and the minimum value is 600 when the checkout is on February 3.

    Answer: The third and fourth formula errors should be:
    =MIN(IF((A2:A7>=F2)*(A2:A7<=G2), E2:E7,””))

    Question: The formula and auxiliary columns can also be used to meet the requirements. That is the day when the minimum value is taken, and the maximum value of that day is taken. Because the amount of data is too much, this is too much trouble, so I want to help vb solve, please help

    Answer: 2018/2/1 results

    Answer: Results for 1-2 days

    Answer: Results for 3-4 days

    Question: The front 600 is right, thank you, can use vba

    Answer: It is ok to use VBA code, but it is not necessary.
    The teacher said that you can use the function without the VBA.

    Answer: The third and fourth formulas can be solved with a pivot table, which is very convenient.

    Answer: PivotTable Animation Demo

    Answer: The above operation error. It should be like this:

    Answer: The code can’t be uploaded, look at the picture

    Eric- January 24, 2019 |