Help on a formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2034377%22%20slang%3D%22en-US%22%3EHelp%20on%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2034377%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20write%20an%20IF%20statement%20for%20a%20column%2C%20and%20I%20will%20use%20the%20same%20formula%20for%20the%20entire%20column.%26nbsp%3B%20%26nbsp%3BExample%3A%26nbsp%3B%20%3DIF(L%2455%2C%26lt%3B%3DH%2457%2C(K2*.10)%2C(K2*.12)).%26nbsp%3B%20L55%20is%20YTD%20gross%20pay%2C%20H57%20is%20the%20tax%20bracket%20of%20the%20least%20amount%2C%20if%20the%20gross%20pay%20is%20now%20higher%20than%20H57%2C%20it%20is%20to%20go%20to%20the%20next%20percentage%20of%2012%25.%20My%20problem%20is%20that%20when%20L55%20is%20greater%20than%20H57%2C%20it%20will%20calculate%20K2*.12%20but%2C%20it%20changes%20all%20the%20previous%20cells%20above%20it%20to%20K2*.12%20instead%20of%20keeping%20it%20as%20K2*.10.%26nbsp%3B%20How%20do%20I%20keep%20the%20previous%20cells%20from%20changing%20values%20once%20the%20statement%20reaches%20false%3F%20This%20is%20for%20a%20personal%20weekly%20payroll%2Fdeduction%20sheet.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2034377%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2034684%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20on%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2034684%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F919046%22%20target%3D%22_blank%22%3E%40Michele70%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20hard%20to%20visualize%20what%20your%20full%20spreadsheet%20looks%20like%20here%2C%20so%20I've%20made%20some%20assumptions.%20If%20this%20isn't%20correct%2C%20could%20you%20please%20post%20a%20copy%20of%20your%20actual%2C%20just%20making%20sure%20no%20real%20names%20are%20included.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEssentially%2C%20you%20want%20to%20have%20a%20YTD%20column%20next%20to%20the%20weekly%20pay%2C%20a%20YTD%20column%20that%20stores%20YTD%20pay%20on%20a%20weekly%20basis%2C%20and%20compare%20that%20figure%20with%20the%20tax%20limit.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20gave%20you%20another%20way%20to%20do%20the%20tax%20calculation%2C%20a%20way%20that%20avoids%20%22hard-coding%22%20the%20tax%20percentage%20in%20the%20formula%20itself.%20This%20involves%20creating%20a%20Tax%20Table...and%20using%20VLOOKUP%20to%20determine%20the%20tax%20rate.%20Using%20a%20table%20like%20this%20enables%20you%2C%20if%20and%20when%20the%20tax%20rate%20changes%2C%20to%20simply%20change%20the%20table%3B%20no%20need%20to%20go%20edit%20every%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I am trying to write an IF statement for a column, and I will use the same formula for the entire column.   Example:  =IF(L$55,<=H$57,(K2*.10),(K2*.12)).  L55 is YTD gross pay, H57 is the tax bracket of the least amount, if the gross pay is now higher than H57, it is to go to the next percentage of 12%. My problem is that when L55 is greater than H57, it will calculate K2*.12 but, it changes all the previous cells above it to K2*.12 instead of keeping it as K2*.10.  How do I keep the previous cells from changing values once the statement reaches false? This is for a personal weekly payroll/deduction sheet. 

2 Replies

@Michele70 

 

It's hard to visualize what your full spreadsheet looks like here, so I've made some assumptions. If this isn't correct, could you please post a copy of your actual, just making sure no real names are included.

 

Essentially, you want to have a YTD column next to the weekly pay, a YTD column that stores YTD pay on a weekly basis, and compare that figure with the tax limit.

 

I also gave you another way to do the tax calculation, a way that avoids "hard-coding" the tax percentage in the formula itself. This involves creating a Tax Table...and using VLOOKUP to determine the tax rate. Using a table like this enables you, if and when the tax rate changes, to simply change the table; no need to go edit every formula.

 

 

@Michele70 I highly recommend @mathetes comments and suggestions but to try and answer your question for learning purposes, the problem is that you are using 2 FIXED cells: L$55 and H$57, and L$55 changes as the year goes on.  What I'm guessing you really want is something more like SUM(L$2:L2) instead of L$55 so that it will dynamically sum column L only up to the corresponding Row instead of using L$55 which I'm assuming is the SUM of all the Rows.