0

I'm trying to calculate a pricing difference when there is a change requested by a customer. The units of measure are E for each, C for per hundred, M for per thousand. My formula is making either every result negative or positive, not taking into account the change noted in Column F. I also tried the formula with an IF of the results of Col E greater than Col D, with the else being multiplied by -1, and vice versa. If more info is needed, I'll be happy to provide it.

HeaderRow

=IF(F2<0,IFS($C2="E",PRODUCT($B2,$F2,,-1),$C2="C",PRODUCT(PRODUCT($B2,$F2,-1)/100),C2="M",PRODUCT(PRODUCT($C2,$F2,-1)/1000)),IFS($C2="E",PRODUCT($B2,$F2),$C2="C",PRODUCT(PRODUCT($B2,$F2)/100),C2="M",PRODUCT(PRODUCT($C2,$F2)/1000)))

Justin
  • 15
  • 1
  • 5
  • Your use of the Product() function makes the formula unnecessarily bulky and hard to read. Is there any reason you don't simply use the multiplication operator `*`? – teylyn Nov 20 '20 at 20:43
  • If C2 contains text "M", your formula wants to multiply that cell?? Or is that a typo and you want B2, like for the other conditions? – teylyn Nov 20 '20 at 20:46
  • What is the question, actually? What do you want to achieve and what does the formula do instead? You describe "My formula is making either every result negative or positive," -- is that the desired outcome? because that is NOT what the formula does right now. It will make ANY result positive, never returns a negative number. Please edit your question and describe the desired outcome. THEN we can look at what formula to use to achieve that. – teylyn Nov 20 '20 at 20:51
  • The goal is to calculate a new PO total after quantity changes are made to a line item. With three different units of measure, I have to multiply B*F and then divide by 100s or 1000s where needed. – Justin Nov 20 '20 at 21:52
  • I need it to calculate negative numbers for a quantity reduction, and positive for an order quantity increase or addition – Justin Nov 20 '20 at 21:53
  • So .... my answer below gives you B*F divided by the order of magnitude. In three variations. If none of these fit the bill, would you care to explain what you really need? – teylyn Nov 21 '20 at 09:34

2 Answers2

0

I assume that multiplying C2 in your formula is a typo, since C2 contains text. I assume you mean B2.

When a positive number is multiplied with a negative number, then the result is negative. That's just simple math. So, if you just want to do that multiplication and then divide the result into 100s or 1000s, you can use something along the lines of

=(B2*$F2)/IFS(C2="e",1,C2="c",100,C2="m",1000)

If, however you WANT to turn every result into a positive result, then you can wrap the result in the ABS() function, which returns the absolute, i.e. always positive, value

=ABS((B2*$F2)/IFS(C2="e",1,C2="c",100,C2="m",1000))

If, however, you want the result to be ALWAYS the exact opposite sign, i.e. you want all negative results to show as positive and all positive results to show as negative, then you need to flip the sign. That can be done by multiplying the end result of the formula with -1, like this:

=((B2*$F2)/IFS(C2="e",1,C2="c",100,C2="m",1000))*-1

I've never seen the Product() function in actual use. I assume it's in Excel for some compatibility purposes with older software, just like the Concatenate() function.

The Multiplication operator * is much easier to read than wrapping operations into Product(), and it's the convention in Excel. You may want to try sticking to that unless there is a specific need to use the Product() function.

teylyn
  • 34,374
  • 4
  • 53
  • 73
0

I fixed this by adding columns for a new line total and net change:

Fixed

Justin
  • 15
  • 1
  • 5