Run a formula from a cell referece

[DylanGrimm]

Is there a way to have a formula in a cell, then have a seperate formula refer to that fomula and run it for a selected value?

So if i have a $0.30 price in a cell, then a Vlookup next to that cell, an that vlookup looks up a seperate table that says for any price under $1.00 use a cell in that row which has an equation in it.


Any suggestions, or similar suggestions please let me know, thank you.

 

[jasonb75]

That doesn't make a lot of sense, try adding a couple of examples.

I'm trying to figure out if you're looking for some beginner advice on formula nesting, or if you want to try and make a formula from concatenated strings.

 

[cb366374]

Like Jason said, your question is a little confusing. Next time, try creating exactly what you're picturing in your mind so it's clear what you're looking for. I'm going to give it a shot for you. Let me know if this is what you had in mind (simplified example).

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]#[/TD]
[TD]Price Floor[/TD]
[TD]Price Ceiling[/TD]
[TD]Formula to use[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]$0.00[/TD]
[TD]$1.00[/TD]
[TD]=vlookup(blah blah)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]$1.00[/TD]
[TD]$5.00[/TD]
[TD]=sumif(blah blah)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]$5.00[/TD]
[TD]$15.00[/TD]
[TD]=count(blah blah)[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]$0.30[/TD]
[TD]=vlookup(blah blah)[/TD]
[/TR]
[TR]
[TD]$5.25[/TD]
[TD]=count(blah blah)[/TD]
[/TR]
</tbody>[/TABLE]


Now you have some cell that holds a price like $0.30. Next to that cell you want it to run vlookup(blah blah). Or if the price cell holds $5.25, you want it to run count(blah blah). The first question would be, why are the formulas so different that you need to structure it this way? I have a feeling you can revamp the formulas you're looking up in the price range table to work in all scenarios.

However, if you really want to set it up with completely different formulas based on the price, I would probably suggest using the CHOOSE function instead. I'll wait for some clarification on your request before diving into that.

 

[DylanGrimm]

My apologies for the confusing question, I'm new into excel functions and bassically i have a table of Acceptable price points as below.
[TABLE="width: 794"]
<colgroup><col style="width: 74pt; mso-width-source: userset; mso-width-alt: 3584;" width="98"> <col style="width: 48pt;" span="15" width="64"> <tbody>[TR]
[TD="class: xl65, width: 98, bgcolor: transparent"]Under $1.00[/TD]
[TD="class: xl66, width: 64, bgcolor: #EEECE1"]0.25[/TD]
[TD="class: xl66, width: 64, bgcolor: #EEECE1"]0.33[/TD]
[TD="class: xl66, width: 64, bgcolor: #EEECE1"]0.39[/TD]
[TD="class: xl66, width: 64, bgcolor: #EEECE1"]0.44[/TD]
[TD="class: xl66, width: 64, bgcolor: #EEECE1"]0.45[/TD]
[TD="class: xl66, width: 64, bgcolor: #EEECE1"]0.49[/TD]
[TD="class: xl66, width: 64, bgcolor: #EEECE1"]0.55[/TD]
[TD="class: xl66, width: 64, bgcolor: #EEECE1"]0.59[/TD]
[TD="class: xl66, width: 64, bgcolor: #EEECE1"]0.65[/TD]
[TD="class: xl66, width: 64, bgcolor: #EEECE1"]0.69[/TD]
[TD="class: xl66, width: 64, bgcolor: #EEECE1"]0.75[/TD]
[TD="class: xl66, width: 64, bgcolor: #EEECE1"]0.79[/TD]
[TD="class: xl66, width: 64, bgcolor: #EEECE1"]0.88[/TD]
[TD="class: xl66, width: 64, bgcolor: #EEECE1"]0.89[/TD]
[TD="class: xl66, width: 64, bgcolor: #EEECE1"]0.99[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]$1.00 - $1.99[/TD]
[TD="class: xl67, bgcolor: transparent"]1.00[/TD]
[TD="class: xl68, bgcolor: yellow"]1.09[/TD]
[TD="class: xl69, bgcolor: transparent"]1.19[/TD]
[TD="class: xl69, bgcolor: transparent"]1.29[/TD]
[TD="class: xl69, bgcolor: transparent"]1.39[/TD]
[TD="class: xl69, bgcolor: transparent"]1.49[/TD]
[TD="class: xl69, bgcolor: transparent"]1.59[/TD]
[TD="class: xl69, bgcolor: transparent"]1.69[/TD]
[TD="class: xl69, bgcolor: transparent"]1.79[/TD]
[TD="class: xl69, bgcolor: transparent"]1.89[/TD]
[TD="class: xl69, bgcolor: transparent"]1.99[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]$2.00 - $2.99[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: #D99795"][/TD]
[TD="class: xl69, bgcolor: transparent"]2.19[/TD]
[TD="class: xl69, bgcolor: transparent"]2.29[/TD]
[TD="class: xl69, bgcolor: transparent"]2.39[/TD]
[TD="class: xl69, bgcolor: transparent"]2.49[/TD]
[TD="class: xl69, bgcolor: transparent"]2.59[/TD]
[TD="class: xl69, bgcolor: transparent"]2.69[/TD]
[TD="class: xl69, bgcolor: transparent"]2.79[/TD]
[TD="class: xl69, bgcolor: transparent"]2.89[/TD]
[TD="class: xl69, bgcolor: transparent"]2.99[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]$3.00 - $3.99[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]3.19[/TD]
[TD="class: xl69, bgcolor: transparent"]3.29[/TD]
[TD="class: xl69, bgcolor: transparent"]3.39[/TD]
[TD="class: xl69, bgcolor: transparent"]3.49[/TD]
[TD="class: xl69, bgcolor: transparent"]3.59[/TD]
[TD="class: xl69, bgcolor: transparent"]3.69[/TD]
[TD="class: xl69, bgcolor: transparent"]3.79[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]3.99[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]$4.00 - $4.99[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]4.19[/TD]
[TD="class: xl69, bgcolor: transparent"]4.29[/TD]
[TD="class: xl69, bgcolor: transparent"]4.39[/TD]
[TD="class: xl69, bgcolor: transparent"]4.49[/TD]
[TD="class: xl69, bgcolor: transparent"]4.59[/TD]
[TD="class: xl69, bgcolor: transparent"]4.69[/TD]
[TD="class: xl69, bgcolor: transparent"]4.79[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]4.99[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]$5.00 - $5.99[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]5.29[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]5.49[/TD]
[TD="class: xl69, bgcolor: transparent"]5.59[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]5.79[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]5.99[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]$6.00 - $6.99[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]6.29[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]6.49[/TD]
[TD="class: xl69, bgcolor: transparent"]6.59[/TD]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl69, bgcolor: transparent"]6.79[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]6.99[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]$7.00 - $7.99[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]7.29[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]7.49[/TD]
[TD="class: xl69, bgcolor: transparent"]7.59[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]7.79[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]7.99[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]$8.00 - $8.99[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]8.29[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]8.49[/TD]
[TD="class: xl69, bgcolor: transparent"]8.59[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]8.79[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]8.99[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]$9.00 - $9.99[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]9.29[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]9.49[/TD]
[TD="class: xl69, bgcolor: transparent"]9.59[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]9.79[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]9.99[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]$10.00 - $14.99[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: #D99795"][/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]xx.49[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: #D99795"][/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]xx.99[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]$15.99 - $19.99[/TD]
[TD="class: xl72, bgcolor: transparent"]min = 15.99[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]xx.49[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]xx.99[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]$20.00 - $29.99[/TD]
[TD="class: xl72, bgcolor: transparent"]min = 21.99[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]xx.49[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]xx.99[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]$30.00 +[/TD]
[TD="class: xl72, bgcolor: transparent"]min = 31.99[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]xx.49[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]xx.99[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

Then i have a list of prices, so i was looking for a way to generate a formula that references this table and spits out the appropriate price point.
For example:
[TABLE="width: 122"]
<colgroup><col style="width: 74pt; mso-width-source: userset; mso-width-alt: 3584;" width="98"> <col style="width: 48pt;" width="64"> <tbody>[TR]
[TD="class: xl71, width: 98, bgcolor: transparent"]Price given[/TD]
[TD="class: xl72, width: 64, bgcolor: transparent"]Acceptable price point[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"] $ 0.34 [/TD]
[TD="class: xl70, bgcolor: transparent"] $ 0.33 [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] $ 1.22 [/TD]
[TD="class: xl66, bgcolor: transparent"] $ 1.19 [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] $ 5.67 [/TD]
[TD="class: xl66, bgcolor: transparent"] $ 5.59 [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] $ 9.86 [/TD]
[TD="class: xl66, bgcolor: transparent"] $ 9.79 [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] $ 10.27 [/TD]
[TD="class: xl66, bgcolor: transparent"] $ 9.99 [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] $ 45.18 [/TD]
[TD="class: xl68, bgcolor: transparent"] $ 44.99 [/TD]
[/TR]
</tbody>[/TABLE]

Right now i have to do these conversions by hand, and i was wondering if their was an formula that could do it for me. I tried making one big IF function but it had too many nested fields since there were 130 or so IF's

 

[jasonb75]

Based on the information in your duplicate thread (please take a moment to read the forum rules and posting guidelines).

Note that the formula can be simplified significantly if you list all of the additional valid prices up to and including 31.99 in the table rather than using the xx.49 or xx.99 format. The formula below is based on all of those prices being listed.

=IF($A2<=31.99,LARGE(IF(Sheet2!$B$1:$P$14<=$A2,Sheet2!$B$1:$P$14),1),FLOOR($A2,0.5)-0.01)

In the formula, the price to lookup was in $A2 and the table was in Sheet2!B1:P14.

 

[DylanGrimm]

Oh got it, again appologies, I wont duplicate a question again.

But okay i think i see what your saying, so make a large table of all possible outcomes and have a formula similar to this reference that. I'll have to flirt around with the LARGE and FLOOR functions since i have never used them before, but you answered my question. Thank you for all your help.

 

[jasonb75]

Sorry, I forgot to say that the formula must be confirmed as an array using Shift Ctrl Enter, otherwise the formula will return a #VALUE! error.

 

[cb366374]

If they were just in a long column of all the acceptable values (A3:A85) you could just use this. No ctrl+shift+enter needed.

=IF(A1>31.99,FLOOR(A1,0.5)-0.01,LOOKUP(A1,A3:A85))