Power Pivot

Resolved · Medium Priority · Version 365

Mon 24 Oct 2022, 16:42 Delegate Marialena said...

Marialena has attended:
Excel Power Query course

Power Pivot

Hi again,

I have an IF(AND(...)) statement: '= IF(AND(x>'y','y' >1,'z' <2),'y',z1 ) and i've tried to use the SWITH(TRUE()) command since i need more than 2 ifs statements but it doesn't give me the right answer because it doesnt take into account the AND part. How can I write the above command properly?

Thanks
Marialena

Tue 25 Oct 2022, 13:38 Trainer Martin said...

RE: Power Pivot

Hello Marialena

Thank you for your enquiry.

I believe what you need to get more than 2 conditions inside an IF function is to use && instead of AND. So your example would look like this:

= IF(x>'y' && 'y'>1 && 'z'<2,'y',z1 )

Please let me know if this solution has worked for you

Kind Regards

Martin

Tue 25 Oct 2022, 13:57 Delegate Marialena said...

RE: Power Pivot

Hi Martin,

Yeah it works now in simple commands, thanks. However, it gets a bit more complicated when I want to do more calculations. For example:

= IF(MIN(IF(x>1,x,10000),IF(y>1,y,10000),IF(z>1,z,10000))=10000,0,MIN(IF(a>1,a,10000),IF(b>1,b,10000),IF(e>1,e,10000)))

In this case, I get an error with MIN. How would that be solved?

Thanks,
Marialena

Tue 25 Oct 2022, 14:38 Trainer Martin said...

RE: Power Pivot

Hi Marialena

Thanks for your response and I'm glad the solution worked.

As for the more complex formula with the IF and MIN, it might help if you could say in plain English what you want to achieve with the formula

We look forward to hearing from you

Kind Regards

Martin

Fri 12 Jan 2024, 00:36 Trainer Rich said...

Will be marked as resolved in 5 days

Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.

Training courses