Excel Table Autofill using IF() Statement

Copper Contributor

I'm trying to build a table in Excel that uses IF() statements to apply different formulas to the same cell based on characteristics in a cell at the top of the page, but running into issues as the columns that contain the IF() formulas don't autofill correctly.

 

Example: We have certain billable rates that are pulled from a VLOOKUP based on input from an adjacent column, but if an individual wants to override that billable rate with a flat rate or a broadly applied % discount they enter at the top of the worksheet. I'd like the formula to update to reflect the new numbers, but also autofill when the individual adds a new row. Thanks for the help!

1 Reply

@JimButler 

You need some extra columns:

Flat rate

Discount %

Rate new

 

In "Flat rate" and "Discount %" you type your overriding values.

Formula in "Rate new":

=IF([@[Discount %]],[@Rate]*(1-[@[Discount %]]),IF([@[Flat rate]],[@[Flat rate]],[@Rate]))