Home

Excel Formulas and Functions

%3CLINGO-SUB%20id%3D%22lingo-sub-531328%22%20slang%3D%22en-US%22%3EExcel%20Formulas%20and%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-531328%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20creating%20a%20spreadsheet%20to%20help%20keep%20logs%20of%20jobs%20I%20do%20daily%20and%20weekly.%20I%20need%20a%20formula%20to%20add%20rows%20in%20a%20column%2C%20then%20subtract%20a%20certain%20amount%20as%20long%20as%20the%20amount%20is%20over%20a%20certain%20amount..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%204%20or%205%20rows%20that%20the%20amounts%20are%20over%20%2451.00%3C%2FP%3E%3CP%3EI%20need%20to%20add%20those%20rows%20and%20then%20subtract%20%2414.00%20from%20each%3C%2FP%3E%3CP%3Eunless%20the%20amount%20is%20under%20%2451.00%20then%20I%20need%20to%20subtract%20%246.00%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-531328%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormula%20help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-531926%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%20and%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-531926%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F333661%22%20target%3D%22_blank%22%3E%40RedRobin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20it%20simplify%20use%20IF%20to%20perform.%20Attached%20is%20a%20sample%20file.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-531992%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%20and%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-531992%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F53148%22%20target%3D%22_blank%22%3E%40Man%20Fai%20Chan%3C%2FA%3E%26nbsp%3B%20.%20I%20see%20your%20formula%20is%20correct%20to%20subtract%20either%2014%20or%206%20based%20on%20column%20A.%20Adding%20or%20deleting%20rows%20are%20VB%2FMacro%20area.%20It%20must%20be%20disappointing%20to%20you%20but%20formula%20only%20can%20do%20affect%20a%20cell%20by%20calculation%20using%20other%20cell%20info.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJ%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-532620%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%20and%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-532620%22%20slang%3D%22en-US%22%3EIs%20there%20a%20way%20to%20do%20it%20so%20it%20calculates%20as%20I%20put%20in%20the%20numbers%20in%20the%20total%20cell%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-532647%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%20and%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-532647%22%20slang%3D%22en-US%22%3EIs%20there%20a%20way%20to%20do%20it%20so%20it%20calculates%20as%20I%20put%20in%20the%20numbers%20in%20the%20total%20cell%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-539089%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%20and%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-539089%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F333661%22%20target%3D%22_blank%22%3E%40RedRobin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20file%2C%20the%20formula%20in%20D7%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DSUMPRODUCT(A2%3AA6-6-(8*(A2%3AA6%26gt%3B%3D51)))%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-540047%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%20and%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-540047%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F333661%22%20target%3D%22_blank%22%3E%40RedRobin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20nothing%20wrong%20with%20an%20%3CSTRONG%3EIF%3C%2FSTRONG%3E%20statement%3B%20it%20makes%20the%20intent%20of%20the%20formula%20clear.%26nbsp%3B%20I%20am%20less%20enthusiastic%20about%20hard-wiring%20dollar%20amounts%20into%20the%20formula.%20%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20since%20you%20are%20likely%20to%20append%20data%20to%20your%20table%2C%20a%20dynamic%20references%20would%20be%20useful%20for%20creating%20totals.%26nbsp%3B%20From%202007%2C%20the%20preferred%20way%20of%20doing%20this%20is%20through%20the%20use%20of%20a%20Table.%26nbsp%3B%20The%20formula%20for%20Adjusted%20Amount%20might%20then%20read%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20%5B%40Amount%5D%20-%20IF(%20%5B%40Amount%5D%26gt%3B51%2C%2014%2C%206)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20also%20possible%20to%20use%20%3CSTRONG%3ELOOKUP%3C%2FSTRONG%3E%20to%20determine%20discounts%2C%20bonus%20rates%20%3CEM%3Eetc%3C%2FEM%3E.%20that%20vary%20as%20one%20moves%20between%20threshold%20values.%26nbsp%3B%20For%20example%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20LOOKUP(%5B%40Amount%5D%2C%20AdjustmentTable)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EThis%20is%20illustrated%20in%20the%20attachment.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
RedRobin
New Contributor

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

@RedRobin 

 

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

@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

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

@RedRobin 

In the attached file, the formula in D7 is: 

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

@RedRobin 

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.

 

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies