Nov 04 2021 02:20 PM - edited Nov 04 2021 02:28 PM
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!
Nov 04 2021 08:03 PM
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]))