Complex formula/equation

[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.

 

[Pete_UK]

I'm still a bit confused, but I think you want something like this:

=MIN(A2,D2) * MIN(C2,F2)

to give you the total price as the product of the smaller quantity and
the lower unit price.

Hope this helps.

Pete

 

[Ashish Mathur]

Hi,

I am not sure I understood your question correctly (despite your elaborate
explanation) but try to enter the following formula in cell I2.

=IF(A2>D2,F2,IF(C2>F2,E2)).

Do let me know if I have misunderstood your question.

--
Regards,

Ashish Mathur
www.ashishmathur.com

"Shaun H. in the United States"