Multiple cell formula to calculate order QTY
Hi All,
I'm trying to create a query that will divide the value of a column depending on the value selected in another.
For context, we need to order a certain number of ink cartridges based on the number of badges we need to print. However, the number of ink cartridges also depends on the type of badge we are printing.
Example:
 If you select the "XXL badge (Black Ink)" we need to divide the total number of badges "QTY" by 400 (Colour) to calculate the number of "Black ink cartridges" we need.
 If you select the "XXL badge (Colour Ink)" we need to divide the total number of badges "QTY" by 2000 (Colour) to calculate the number of "Colour ink cartridges" we need.
As the value in the "Badge type" changes per row, I need a query that will do this automatically based on the "badge type" value will apply the correct sum.
To write the query out simply:
 If "Badge Type" = XXL (Black Ink) divide the "QTY" of badges by 400
 If "Badge Type" = XXL (Colour Ink) divide the "QTY" of badges by 2000
 If "Badge Type" = CR80 (Black Ink) divide the "QTY" of badges by 1000
 If "Badge Type" = CR80 (Colour Ink) divide the "QTY" of badges by 200
Cells and values below (screenshot)
Would really appreciate the help to:
 Confirm this is possible? (Ha)
 Help me with the initial query/formal string
Thanks
Dave
Best Answer

Hi David,
Try this:
=IF([Badge Type]@row="XXL (Black Ink)",[Badge QTY]@row/400,IF([Badge Type]@row="XXL (Colour Ink)",[Badge QTY]@row/2000,IF([Badge Type]@row="CR80 (Blank Ink)",[Badge QTY]@row/1000,IF([Badge Type]@row="CR80 (Colour Ink)",[Badge QTY]@row/200,""))))
The "" at the end states that if none of the criteria are met, it will display a blank cell. Hope this helps!
Best,
Heather
Answers

Hi David,
Try this:
=IF([Badge Type]@row="XXL (Black Ink)",[Badge QTY]@row/400,IF([Badge Type]@row="XXL (Colour Ink)",[Badge QTY]@row/2000,IF([Badge Type]@row="CR80 (Blank Ink)",[Badge QTY]@row/1000,IF([Badge Type]@row="CR80 (Colour Ink)",[Badge QTY]@row/200,""))))
The "" at the end states that if none of the criteria are met, it will display a blank cell. Hope this helps!
Best,
Heather

Many thanks Heather, this really help and more importantly, worked!

Fantastic! Happy to help. Have a great weekend.
Help Article Resources
Categories
Check out the Formula Handbook template!