Forum Discussion
Attempting to apply discount formula to column of data
Right now I have a excel sheet with 2800+ different data points (Current Cost) in a column. Currently I have the percentage in its own cell (L10) so that I'm able to change it and have it adjust the associated data. As well as I'm trying to use the formula,
"=(Current Cost)*(1-L10)"
With the all the current cost's already typed out in the column of data. But what I'm wondering is, is there a way that I can apply that formula to the column all at once? Because I don't want to go through the painful process of typing it in every cell. The reason I'm having trouble is all the costs are different, making it so I'm not able to just drag down and apply the formula since the data is already in place.
If any of this is unclear or needs described better, feel free to ask any questions. Thank you in advance!
11 Replies
Better to see small sample. You can't drag formula down since you already have some data in place. Thus you'd like to apply the formula not to entire column, but only to cells with no data in them, correct? Or what?
The data is already listed out in a column, but hoping to apply a formula. But the problem I'm running into is that there is part of the formula (cost) that changes in every cell. So I'm trying to apply the formula without having to change the data that's already provided. But yes, I would like to apply the formula to the entire column.
I'll attach the file to explain better. On the second sheet, the percentage is in cell L10, and I would like to apply the formula to column F in cells 9-2885. With the current formula being "(Cost)*(1-L10)"
Edit: I noticed I was a bit delayed in posting lol
You need an additional helper column for the calculation. You don't really want to apply the formula to the same column as the values reside. That presents a whole host of issues. While you could do this with VBA, I certainly would never recommend it. Best to add another column, like @wumolad did in their example file.
Additionally, if you had your data in a Table, like the sample file from @wumolad, you can make use of structured referencing. For example, your 'total chickens' calculation would then look like this (all formulas below are assuming you have Table'd your data):
=IF(Permit,[@[avg Mplier]]*Hatchings*[@[max time]]*4,IF(LEN([@Combination])<4,[@[avg Mplier]]*Hatchings*[@[max time]]*4,0))Furthermore, you can condense these formulas a bit, since there's repetitions. In the above example, you could probably just shorten to:
=IF(AND(Permit,OR(Permit,LEN([@Combination])<4)),[@[avg Mplier]]*Hatchings*[@[max time]]*4,0)Using a Table would also shorten formulas like your 'Input' calculation of 'Optimal boos' in B17:
{=IF(Chickens,INDEX(Table1[Combination],MATCH(LARGE(OFFSET(INDEX(Table1[total chickens],1),0,0,MATCH(Input!B18,Table1[Combination],0)-1),1),Table1[total chickens],0)),"")}
NOTE: Excel automatically adds the { } curly brackets when you confirm with CTRL+SHIFT+ENTER.Your formulas in Input!C17:C20 don't need to be array entered.
I'm assuming all of the blank rows are there because this originated somewhere as a Google Sheet (based on the 1000 rows and all). I don't see a purpose for hiding those rows as they don't seem to be used anywhere else.
I'm curious where the data in the 'Database' sheet came from though. Most of it is manually entered (makes me exhausted just looking at it!). Is this data sourced anywhere? Does it come from inputs? Will it ever change? I'm wondering if there may be an easier way to get your Table of values.
