Mutiple Math Operations in the Same Cell

[zgreen]

Apologies if this has been answered (a post from 2020 didn't solve it and I can't find anything else)....

I want to create a formula that adds two cells THEN divides the result by a third cell.
eg. (B6+B7)/G23

Alternatively, I'd like to add multiple cells in the denominator as well (like the numerator).
eg. (B6+B7)/(G12:G23)

Sounds simple enough but I always get a result of "1."
What am I doing wrong?

TIA

 

[Switters123]

What is the actual data in the cells? Are all the cells formatted as numbers? Is the result of your formula a number near one, so it appears as 1 due to rounding?

 

[jtakw]

Hi,

I'm surprised you got any answer at all, as you can't do a division by a range of cells in this manner, if anything, you would get #VALUE! error.

Are you trying to divide by the Total of the range?

=(B6+B7)/SUM(G12:G23)

 

[joeu2004]

I'm surprised you got any answer at all, as you can't do a division by a range of cells in this manner, if anything, you would get #VALUE! error.

Yes we can. Learn about "implicit intersection", before MSFT added the "@" operator. One explanation: Excel Implicit Intersection | Exceljet .

In a nutshell, the formula might work when it is entered a cell in rows 12 to 23, but only if the reference cell in column G is non-zero.

For more explanation, see Excel Implicit Intersection | Exceljet .

(Note: I have not really vetted that explanation in detail. It just looks like it has the "right idea", after a quick scan.)

But I agree: that is not likely to be zgreen's intent. I agree with jtakw's guess.

 

[zgreen]

Correction, my result is always "0" (at one point it was "1" but I can't replicate that anymore so not sure what I did)

#jtakw - yes, I'm wanting to divide by a range (array?) of values. Having said that, I'd also like to be able to divide by adding multiple cell values in brackets

#joeu2004 - I have to admit that I only half understood that article. I was hoping for something more obvious/simple

Specifically, I'm building a template where I would like to plug in values from other cells. Some of the cell values are simple numbers (entered manually) where other numbers have already been SUM-ed or PRODUCT-ed but I need that value for my formula. (Hopefully I explained that clearly.)

 

[joeu2004]

#joeu2004 - I have to admit that I only half understood that article. I was hoping for something more obvious/simple

My response was for jtakw's benefit, not for you.

 

[jtakw]

I'm still unclear on what you want.

You should post Sample Data and expected Results, with an explanation. Preferably using XL2BB, not a picture (see my signature).