May 04 2019 12:35 PM
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
May 04 2019 06:25 PM - edited May 04 2019 06:25 PM
May 04 2019 11:06 PM
@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
May 05 2019 09:45 AM
May 05 2019 10:33 AM
May 06 2019 08:43 PM
May 07 2019 02:29 AM
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
= LOOKUP([@Amount], AdjustmentTable)
This is illustrated in the attachment.