• 100% Satisfaction Guarantee
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1936
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now

# I'm trying to use a formula to return a value using multiple

### Customer Question

I'm trying to use a formula to return a value using multiple conditions as shown in this table:
FE Gross PVR
Units 1,450 1,500 1,550 1,600 1,650 1,700
220 32 33 34 37 39 40
210 31 32 33 36 38 39
200 30 31 32 35 37 38
190 26 27 29 32 33 34
180 25 26 28 31 32 33
170 24 25 27 30 31 32
Example: If Units are >189 and less than 1499 and less than 1550, then return the value of 27.
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  Richard replied 1 year ago.

Hi there and welcome

You would want this formula assuming the values are in A1

=if(and(A1>189,A1<1550),27,0)

You do not need to do less than 1499 and less than 1550 as if its less then 1550 then it is also less then 1499

Let me know how you go with it please

Customer: replied 1 year ago.
The example was just one scenario. I need the formula to return the value in the table based on the Units and FE Gross PVR. Let's presume Units are in A1 and FE Gross PVR is located in A2. I need to input the variable unit number in A1 and the also variable FE Gross PVR in A2 and the formula return the corresponding value from the table in A3.The paste feature did not show the table very well, but the values 32, 33, 34, 37, 39 & 40 are adjacent to 220 and under 1450, 1500, 1550, 1600, 1650 and 1700. The other values follow the same pattern. Make sense?
Expert:  Richard replied 1 year ago.

No sorry, but explain the formula in your own words so I can convert to excels syntax

so example if A1 = this and A2 = that then A3 will equal this else that

Expert:  Richard replied 1 year ago.

where you able to tell me this please?

Customer: replied 1 year ago.
I am leaving for lunch, but will be available from 2:00 - 2:45 EST. Thank you.
Expert:  Richard replied 1 year ago.

ok, I work online here as its easier then via the phone.

Let me know please when you have the information

Customer: replied 1 year ago.
We will pay our managers based on the grid I sent earlier. Their bonus multiplier is determined based on a combination of Units sold and FE Gross PVR. If they sell 190 units with a \$1450 FE Gross PVR, their bonus multiplier is 26. The next month, if they sell 200 Units with a \$1550 FE Gross PVR, their multiplier is 32. The following month, if they sell 210 Units with a \$1650 FE Gross PVR, their multiplier is 39.I need to input Units and FE Gross PVR and have the Multiplier (Value) from the center of the grid returned.
Expert:  Richard replied 1 year ago.

ok, but how I need you to explain the actual rules

So for example

It needs to check if the units in A1 is greater then 190, and the cost in B1 was 1450, then return 100, else return 50

So I can code the formula

Customer: replied 1 year ago.
I'm sorry this isn't working. I will resolve a different way. Thank you.
Expert:  Richard replied 1 year ago.

ok, but I am not sure why you would not do as I requested....

Just explain the rules in your own words and I can code the syntax.

I marked the question to never close in case you change your mind later.

If you would like any additional information or assistance, please do not hesitate to let me know.

And if you can take a second to rate my service by clicking one of the stars at the top of the screen then submit, that would be greatly appreciated.

Thank you

Expert:  The-PC-Guy replied 1 year ago.

i would like to help you with this and i think the solution is probably quite simple, it would be best to get a visual idea of how your sheet is set up. If you could send the actual excel file or something with some example data arranged the same way. I could assist you further

you could send the file to

www.wikisend.com, you do not need to create account to use the service and it is free.

if wikisend does not work you can try,

ge.tt

sendspace.com

or any other file sharing service you use such as drop box or google drive.

just paste the file sharing link for whatever service you decide to user

Expert:  The-PC-Guy replied 1 year ago.

it would be helpful to view the worksheet

ou could send the file to

www.wikisend.com, you do not need to create account to use the service and it is free.