Highlighted
New Contributor

# Excel Formulas and Functions

I'm creating a spreadsheet to help keep logs of jobs I do daily and weekly. I need a formula to add rows in a column, then subtract a certain amount as long as the amount is over a certain amount..

Example;

I have 4 or 5 rows that the amounts are over \$51.00

I need to add those rows and then subtract \$14.00 from each

unless the amount is under \$51.00 then I need to subtract \$6.00

6 Replies
Highlighted

# Re: Excel Formulas and Functions

Can it simplify use IF to perform. Attached is a sample file.

Highlighted

# Re: Excel Formulas and Functions

@Man Fai Chan  . I see your formula is correct to subtract either 14 or 6 based on column A. Adding or deleting rows are VB/Macro area. It must be disappointing to you but formula only can do affect a cell by calculation using other cell info.

J

# Re: Excel Formulas and Functions

Is there a way to do it so it calculates as I put in the numbers in the total cell?
Highlighted

# Re: Excel Formulas and Functions

Is there a way to do it so it calculates as I put in the numbers in the total cell?
Highlighted

# Re: Excel Formulas and Functions

In the attached file, the formula in D7 is:

=SUMPRODUCT(A2:A6-6-(8*(A2:A6>=51)))

Highlighted

# Re: Excel Formulas and Functions

There is nothing wrong with an IF statement; it makes the intent of the formula clear.  I am less enthusiastic about hard-wiring dollar amounts into the formula.

Also, since you are likely to append data to your table, a dynamic references would be useful for creating totals.  From 2007, the preferred way of doing this is through the use of a Table.  The formula for Adjusted Amount might then read

= [@Amount] - IF( [@Amount]>51, 14, 6)

It is also possible to use LOOKUP to determine discounts, bonus rates etc. that vary as one moves between threshold values.  For example