If, And, Or formula

Julie Caliri

Looking to calc MQLs if FY = FY24 (easy part), but each region has a different conversion rate and that's where I'm struggling.

My formula is something along the lines of:

IF ([FY]@row = "FY24"), and (or ([Region]@row is "ANZ", multiply [Lead forecast]@row * 25%), ([Region]@row is "ASEAN", multiply [Lead forecast]@row * 35%), ([Region]@row is "China", multiply [Lead forecast]@row * 26%))

but it's not quite working.

this works but I'm struggling to expand it: =IF(AND(Region@row = "ANZ", FY@row = "FY24"), [Leads Forecast]@row * 0.25)

thanks~

Scott Orsey

Hi @Julie Caliri , You may be trying to do too much with one formula. Also, taking this approach may make changes in the future more difficult. I would recommend the following steps which may seem a bit harder now but will make your solution scalable and updatable in the future:

1) Create a second sheet with two columns: Region and Conversion Rate. In this sheet, add a row for each region and then populate the appropriate conversion rate.

2) In your main sheet, add a helper column. Let's also call it Conversion Rate. In this column, create a formula using the INDEX(MATCH()) structure to look up the region from the second sheet and return the appropriate conversion rate. If you haven't done this before, give it a try by using the online help. If you can't figure it out, reply with how far you've gotten and I (or others) can help out.

3) Calculate the MQL by multiplying [Lead forecast]@row * [Conversion Rate]@Row

I hope this helps! Be well.