Run formula based on value of another column

rsiem1

New Member
Joined
May 20, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
I am using Excel 2016. I have 2 columns: Method (Column A) and Price (Column B).
The Method column has 2 possible values: Flatbed or Van. The Price column has decimal values.
I want to run the formula: =20+80*RAND() to generate random decimal numbers into the Price column (Column B) based on if the value in Method (Column A) is equal to the text/value of "Flatbed".
I have tried using the IF function of IF( =A2="Flatbed",=B2=20+80*RAND(), 0 ) but that didn't work.

What is the method or formula that will run the =20+80*RAND() function to populate the Price column if the Method column = Flatbed ?

Thanks.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
707
Office Version
  1. 365
Platform
  1. Windows
See if this could help.

In B2 and drag down if needed.

VBA Code:
=IF(A2="Flatbed",20+80*RAND(),"")
 
Solution

rsiem1

New Member
Joined
May 20, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
See if this could help.

In B2 and drag down if needed.

VBA Code:
=IF(A2="Flatbed",20+80*RAND(),"")
Thank you very much - this formula worked correctly! :)

FYI - Because I have 2 possible rates Van and Flatbed I needed to have random generated rates for both but separate series values (i.e. Flatbed rates under (<) $ 100 and Van rates more than(>) $ 100. I played around with your formula and got the following to work:

=IF(A2="Van",90+200*RAND(),10+80*RAND() )

Question: Is it possible to have a formula similar to above that would have 3 clauses: one random rates for Van, one random rates for Flatbed and 0 if neither is true?
Something like =IF(A2="Van",100+200*RAND(),IF (A2="Flatebed",100+80*RAND(), 0 )

pseudo code would be IF A2= "Van" then x, IF A2="Flatbed" then y, IF not "Van" or "Flatbed then z (i.e. not true)

Thanks.
 

rsiem1

New Member
Joined
May 20, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Thanks for the newest formula. Tried it and it works for looking at both the Van and Flatbed rates.

It appears that you can have multiple IF statements in a formula - is there a limit to how many are allowed (3, 4, 5) ?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,579
Messages
5,770,960
Members
425,653
Latest member
UNSING

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top