S
Shaun H. in the United States
Good evening:
What is the easiest way to perform the following:
On one side, I have Column A (the quantity actually purchased and ordered),
B (the unit price), C (the total price), and on the other side are Column D
(the estimated quantity needed), E (the estimated unit price), F (the
estimated total price). I already have two additional columns G (a quantity
variance) and H (a total price variance). I am attempting to distinguish the
two in a totally separate column, column I.
Also, if column D is greater than column A or if F is greater than C, then
column I
needs to be the lessor (the lessor quantity and the lessor price). I only
want one number to be reflected.
***********************
Ex: Column A= 85 (the quantity actually purchased and ordered), B=$2.15 (the
unit price), C= $182.75 (the total price), and on the other side are Column
D= 75 (the estimated quantity needed), E= $1.59 (the estimated unit price),
F= $119.25 (the estimated total price). The two additional columns G= -(10)
(a quantity variance) and H= -($63.50) (a total price variance).
Although a quantity of 85 (A) were actually purchased, only the quantity of
75 (D) will be allowed. Likewise, the total dollar amount of $119.25 (F)
cannot be exceeded.
If the D or F figures are greater, then the A or C figures will be used.
************************
Column I should be only one figure, the lessor of the two.
In other words, I would like column I to state "If column A is greater than
column D, then Column I should be column F AND/OR if column C is greater than
column F then te price in column F should be reflected. I would like all of
that to be column I. Is there a formula to include both equations?
Thank you very much.
What is the easiest way to perform the following:
On one side, I have Column A (the quantity actually purchased and ordered),
B (the unit price), C (the total price), and on the other side are Column D
(the estimated quantity needed), E (the estimated unit price), F (the
estimated total price). I already have two additional columns G (a quantity
variance) and H (a total price variance). I am attempting to distinguish the
two in a totally separate column, column I.
Also, if column D is greater than column A or if F is greater than C, then
column I
needs to be the lessor (the lessor quantity and the lessor price). I only
want one number to be reflected.
***********************
Ex: Column A= 85 (the quantity actually purchased and ordered), B=$2.15 (the
unit price), C= $182.75 (the total price), and on the other side are Column
D= 75 (the estimated quantity needed), E= $1.59 (the estimated unit price),
F= $119.25 (the estimated total price). The two additional columns G= -(10)
(a quantity variance) and H= -($63.50) (a total price variance).
Although a quantity of 85 (A) were actually purchased, only the quantity of
75 (D) will be allowed. Likewise, the total dollar amount of $119.25 (F)
cannot be exceeded.
If the D or F figures are greater, then the A or C figures will be used.
************************
Column I should be only one figure, the lessor of the two.
In other words, I would like column I to state "If column A is greater than
column D, then Column I should be column F AND/OR if column C is greater than
column F then te price in column F should be reflected. I would like all of
that to be column I. Is there a formula to include both equations?
Thank you very much.