ifs formula

[CSHOCK]

I'm trying to write a formula that figures the rate if a location is placed in a certain cell

=b15*IFS((B17=Kerry,85)(B17=VPGC,90)(b17=Cargill,90))

This is what I've written and I've tried multiple configurations of this but none of them seem to work.

help?

 

[Joe4]

Welcome to the Board!

Note that all literal values need to be placed between double-quotes, i.e. "Kerry", "VPGC", "Cargill".
Otherwise, they will be treated as named ranges and not literal values.

Also, your IFS formula is not structured right. You need commas between all the arguments, as described here: IFS function - Microsoft Support

So perhaps it should look like this:

=B15*IFS(B17="Kerry",85,B17="VPGC",90,B17="Cargill",90)

 

[Fluff]

Hi & welcome to MrExcel.
What version of Excel are you using? Your profile shows 2003 or older, but that doesn't have the IFS function.

 

[CSHOCK]

Hi & welcome to MrExcel.
What version of Excel are you using? Your profile shows 2003 or older, but that doesn't have the IFS function.

honestly I have no idea it's a work computer - 365 I think

 

[CSHOCK]

Welcome to the Board!

Note that all literal values need to be placed between double-quotes, i.e. "Kerry", "VPGC", "Cargill".
Otherwise, they will be treated as named ranges and not literal values.

Also, your IFS formula is not structured right. You need commas between all the arguments, as described here: IFS function - Microsoft Support

So perhaps it should look like this:

=B15*IFS(B17="Kerry",85,B17="VPGC",90,B17="Cargill",90)

fan effing tastic!!! Thank you!!!!!

 

[Joe4]

You are welcome.
Glad we were able to help!

 

[Scott Huish]

Since you're only testing 1 cell, you could use SWITCH:
=B15*SWITCH(B17,"Kerry",85,"VPGC",90,"Cargill",90)

 

[Peter_SSs]

honestly I have no idea it's a work computer - 365 I think

In Excel on your work computer, click File -> Account and look for something like this

You can update your profile with both work and home versions and when you ask a question, tell the readers which version (or versions) any suggestion needs to work in.

For what its worth, I would not use IFS (not only for this situation but for any) as it is inefficient in my view.
The issue I have with it is that it evaluates every condition, even if the first one is true.

=B15*IFS(B17="Kerry",85,B17="VPGC",90,B17="Cargill",90)

As an example, let's suppose that B17 contains "Kerry"
IFS will evaluate the blue condition and find that it is TRUE.
In any case it will then evaluate the red condition and find it is FALSE
and it will then evaluate the purple condition and find that it is also FALSE
Now it will go back and see that the first TRUE condition is the first one (blue) and return 85. In other words the red test and the purple test were just wasting time & resources.