Forum Discussion
Attempting to apply discount formula to column of data
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.
Check the updated version with table and another column (Disc Cost) in column J.
Hope this helps
Yes, that better accomplished what I was intending. But could you explain what the function you used to accomplish this?
"=+[@Cost]*(1-$L$10)"
I'm not bad with excel I'm just not used to using the shortcuts and things. Like the + at the beginning and dollar signs are the things I'm confused about.
