I was using this formula in a spreadsheet: SUM(B12:H12/B$8:H$8)/(I12+J12+K12-L12-M12)

and was getting an answer of 276.37%, but when I went into the cell clicked on the formula and then entered out of the cell the cell value changed to #Value!. The values of the cells are

B8: 19 B12: 482 I12: 35

C8: 10 C12: 41 J12: 2.5

D8: 8 D12: 382 K12: 1.5

E8: 22 E12: 0 L12: 5.25

F8: 35 F12: 562 M12: 0

G8: 25 G12: 0

H8: 19 H12: 0I have been staring at this formula for a day and I cannot seem to duplicate the 276%. The formula is not mine, but apparently the 276% is the correct value. Could someone help me change the formula in order to get this result?

Thank you

It was probably an array formula. Put you cursor back in the cell and press Ctrl-Shift-Enter.

This will put curly brackets { } around the formula and tell Excel that it is an Array formula.

You can't just type in the { }. You have to use Ctrl-Shift-Enter every time you edit the formula.

Wow can't believe I didn't think of that. Guess it just takes a fresh pair of eyes. Thank you.

Ask Your Question

Weekly Poll

Do you think Jony Ive could make a big impact on Airbnb?

Discuss in The Lounge

Poll History