Why won't my formula work? It has the triangle with an exclamation point

Question

Level 1

4 points

Why won't my formula work? It has the triangle with an exclamation point

SUM((C3:E3×Cost 'Swiftly Tech Racerback Tank 2.0 *Race')+(F3×Cost 'Swiftly Tech Short Sleeve 2.0 “Race')+(G3:I3+Cost 'It'’s Rulu Run Cropped 1/2 Zip')+(J3:L3×Cost Pace Rival MR Skirt WHT)+(M3:O3×Cost Pace Rival MR Skirt TRNV)+(P3:Q3×Cost 'Scuba Oversized Half-Zip Hoodie')+(R3:U3×Cost 'Scuba High-Rise Jogger TRNV')+(V3:W3×Cost ABC Jogger BLK)+(X3:Z3×Cost 'License to Train Hoodie ')+(AA3×Cost Restful Slide Black)+(AB3×Cost Ventilated Tennis Short Sleeve)+(AC3:AE3×Cost 'Soft Jersey Classic - Fit MR 7/8 Jogger TRNV'))

Posted on Feb 22, 2024 6:50 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

31,269 points

Feb 22, 2024 7:15 AM in response to lion_in_zion

The triangle will tell you what it thinks is wrong. That is a start. Turning off "use header names as references" in Numbers Preferences makes it a whole lot easier to read and diagnose formulas.

What I think I see right off the bat is you are trying to multiply ranges by single values. For example,

C3:E3×Cost 'Swiftly Tech Racerback Tank 2.0 *Race'

This does not work in Numbers. it looks like you are writing an Excel array formula. Array formulas are not supported in Numbers.

If you are trying to convert or use an Excel array formula, you will have to rewrite it as a regular formula. It is difficult for us to read it and rewrite it with all the "header names used as cell references".

To get you started (maybe), is C3:E3×Cost 'Swiftly Tech Racerback Tank 2.0 *Race' supposed to multiply the cells C3:E3 by the single value Cost 'Swiftly Tech Racerback Tank 2.0 *Race' and sum them together? if so, that piece would become SUM(C3:E3)×Cost 'Swiftly Tech Racerback Tank 2.0 *Race'

You are using SUM to sum the values but also using + between them all to sum them up. SUM redundant, the values are already summed before you apply SUM.

Reply

Answer 2

Badunit

Level 7

31,269 points

Feb 22, 2024 7:55 AM in response to lion_in_zion

I am not sure what result you are expecting.

In Excel,

SUM((C3:E3*2) + 5) gives me C3*2 + D3*2 + E3*2 + 5*3

SUM((C3:E3*2,5) gives me C3*2 + D3*2 + E3*2 + 5

So it appears that each of the single values in your sum will be multiplied by 3 to match up with there being three items in the ranges. Is this what you are going for?

I'm not going to try rewriting your entire formula but here is how I'd do it:

Remove the outer SUM formula. It is unnecessary
Replace (C3:E3×Lululemon Orders by Item::B2) with SUM(C3:E3)×Lululemon Orders by Item::B2
Repeat step 2 for all the other pieces that use ranges multiplied by a single value
(G3:I3+Lululemon Orders by Item::B10) I think should have been multiplication, not addition. If that is correct, do to it like in step 2 and change its + to ×
Gather all the items that are single values like (F3×Lululemon Orders by Item::B7) and add them together within a single set of parentheses then multiply it by 3.
Keep the +'s between each of the individual pieces

I wonder if rearranging your spreadsheet in some way might make this formula a lot simpler so it is not jumping all around the place.

Reply

Answer 3

lion_in_zion Author

Level 1

4 points

Feb 22, 2024 7:14 AM in response to Badunit

SUM((C3:E3×Lululemon Orders by Item::B2)+(F3×Lululemon Orders by Item::B7)+(G3:I3+Lululemon Orders by Item::B10)+(J3:L3×Lululemon Orders by Item::B15)+(M3:O3×Lululemon Orders by Item::B20)+(P3:Q3×Lululemon Orders by Item::B25)+(R3:U3×Lululemon Orders by Item::B29)+(V3:W3×Lululemon Orders by Item::B35)+(X3:Z3×Lululemon Orders by Item::B39)+(AA3×Lululemon Orders by Item::B44)+(AB3×Lululemon Orders by Item::B47)+(AC3:AE3×Lululemon Orders by Item::B50))

This is the formula with it turned off. I learned formulas on excel and obviously don't know how to translate that to numbers. It says I can't use C3:E3 as a single value. What do I need to change and how do I do these formulas in Numbers? I would love to learn their formula system but everything I find online are simple formulas.

Reply