Multiple cell formula to calculate order QTY

David Slaughter

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:

1. 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.
2. 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:

1. Confirm this is possible? (Ha)
2. Help me with the initial query/formal string

Thanks

Dave

Heather Duff

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

Heather Duff

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

David Slaughter

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

Heather Duff

Fantastic! Happy to help. Have a great weekend.