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

Highlighted
Is there a way to do it so it calculates as I put in the numbers in the total cell?
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
Stable version of Edge insider browser
HotCakeX in Discussions on
35 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