0

I have one drop down cell that contains choices of "A Company", "B Company". The next cell has a drop down with choices of "Founder", "Director", "Lead". I'm trying to right a formula to calculate the following:

If cell 1 is "A Company" and next "Founder" then I want the cell to reflect the result of "220"
If cell 1 is "A Company" and next is "Director" then I want the cell to reflect result of "200*.9"
If cell 1 is "A Company" and next is "Lead" than I want the cell to reflect result of "200*.7"
If cell 1 is "B Company" and next is "Founder" then I want the cell to reflect the result of "175"
If cell 1 is "B Company" and next is "Director" then I want the cell to reflect result of "175*.9"
and so forth
Is it possible to create a formula that will do that?

2
  • I suspect that your real sheet doesn't contain just two companies, and that you likely want to process more than one row of such data. But it's not efficient to write complex formulas around guesswork. If you want to share a link to the spreadsheet (or to a copy of it, or to a realistic mock-up of it setup the same way and with enough limited, sanitized data to fully illustrate the goal, I'm take another look.
    – Erik Tyler
    Apr 5, 2022 at 16:02
  • Of course Erik is right, it would be much better to create a second table with the various values ​​and then get them from there
    – Daniele
    Apr 5, 2022 at 16:18

1 Answer 1

0

if the two drop-down cells are A2 and B2:

=Ifs(
or( A2="", B2=""),"",
and(A2="A Company",B2="Founder"),220,
and(A2="A Company",B2="Director"),220*0.9,
and(A2="A Company",B2="Lead"),220*0.7,
and(A2="B Company",B2="Founder"),175,
and(A2="B Company",B2="Director"),175*0.9,
and(A2="B Company",B2="Lead"),175*0.7
)

IFS

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.