excel 公式運算

You may use the Data Validation function to restrict the data entry.
The first row will be restricted to constant value 30000, the subsequent rows 2 to 23 will depend on the sum of above rows on column C whether exceed 30000 or not:
1. Select cell ranges A1:B1 and D1 (press the Control key for multiple non-neighbouring cell range selection)
2. Select menu Data --> (Data Tools Tab) Data Validation…
3. Select options under (i) Allow: ‘Decimal’ or ‘Whole number’; (ii) Data: ‘less than or equal to’; and put the number under (iii) Maximum: ‘30000’
4. Select OK
5. Select cell ranges A2:B23 and D2:D23
6. Select menu Data --> (Data Tools Tab) Data Validation…
7. Select options under (i) Allow: ‘Decimal’ or ‘Whole number’; (ii) Data: ‘less than or equal to’; and put formula under (iii) Maximum: ‘=30000-SUM($C$1:$C1)’
8. Select OK
Now you may test data entry and see if the restriction is effected.