Complicated IF/OR formula needed

[juliejo]

Hello! I have a messy formula that I need to figure out. I have a list of several options. Most of them require a common formula, but two of them require unique formulas. They all use the same field though, so I gotta figure out how to put them all together.

So let's say that I have Option1 thru Option7. For Option3 thru Option7, I need to return the formula H55*H56. If Option1 is chosen, then I need to use formula (H55*H56)-400. If Option2 is chosen, then I need formula (H55*H56)-500. Also, the results need to roundup (just to keep it really interesting).

Here's the formula that I have so far, which only incorporates Option1 (ignore the IFERROR).

=IFERROR(IF(A55="Option1",ROUNDUP(((H55*H56)-400),0),ROUNDUP((H55*H56),0)),"")

How do I get the Option2 conditions/formula into this? Or do I need to do it completely differently?

Thanks!

[#2]

@juliejo 

=IF(A55="Option1",ROUNDUP(((H55*H56)-400),0),

IF(A55="Option2",ROUNDUP(((H55*H56)-500),0),ROUNDUP(((H55*H56)),0)))

This could be what you are looking for.

[#3]

@juliejo 

Try:

=IFERROR(ROUNDUP(H55*H56
- IF(A55="Option 1", 400, IF(A55="Option 2", 500, 0)), 0), "")

[#4 · Accepted solution]

@juliejo 

=ROUNDUP(H55*H56+SWITCH(A55,"Option1",-400,"Option2",-500,0),0)