Hello all,
I am trying to translate an excel =IFS formula I have working on an excel doc into Monday folmulas.
My formula looks at available data from six columns to pull up a result in a seventh. At any point in time some of the columns could be blank and the formula would need to pull the information from the available data from other columns instead. The final goal of the formula is a division.
My columns in excel:
Column E6: holds the final formula
Column G6: $ final price
Column H6: $ price (as provided by external source)
Column I6: $ number column (estimated internal price)
Column J6: $ number column (as column G * column O)
Column O6: (%) multiplier for column J (as 1.2 = 20%)
Column AJ6: $ (%) multiplier for additional costs (as 1.2 = 20%)
the four formula options that need to work:
Formula 1 =G6/(J6 * AJ6)
Formula 2 =G6/(H6 * AJ6)
Formula 3 =G6/(I6 * AJ6)
Formula 4 =G6/(I6)
in excel my formula reads as follows:
=IFS(J6>0,(G6/(IF(AJ6=0,J6,J6*AJ6))),H6>0,(G6/(H6*AJ6)),G6>0,(G6/(I6*AJ6)),AJ6<1,6/I6),TRUE,"")
I only needed this in excel to eliminate the #DIV/0 error → IF(AJ6=0,J6,J6*AJ6)
Monday does not have an =IFS formula so I assume I will need to use IF, AND, OR formulas to get the same calculations. I used the AI formula builder to help alog the way and it spit out this:
current monday formula
IF({J6} > 0, {G6} / IF({AJ6} = 0, {J6}, {J6} * {AJ6}), {H6} > 0, {G6} / ({H6} * {AJ6}), {G6} > 0, {G6} / ({I6} * {AJ6}), {AJ6} < 1, {MSRP} / {I6}, TRUE, "")
So far only this part of my excel formula (in bold) is working. I can’t figure out the rest:
=IFS(J6>0,(G6/(IF(AJ6=0,J6,J6*AJ6))),H6>0,(G6/(H6*AJ6)), G6>0,(G6/(I6*AJ6)),AJ6<1,(G6/I6),TRUE,“”)
Anyone out there have success with something similar or ideas how to adjust the Monday formula?
Thanks! -A