I've been trying to create a formula which determines the size (Weighting) dynamically of the square based on the rows and columns and haven't had any luck. Hope the images explain. Thanks!

jacksaxby Well, there is a saying that "a picture is worth a thousand words," but, sadly, it's not always true. At least not when it comes to creating Excel formulas. Better would be words that explain with an example or two what you expect the result to be for several of those sets of figures. There's also a bit of potential confusion (at least in my mind) around whether there's a distinction between size and weight. The way you write, "a formula which determines the size (Weighting)" seems to equate weight--or, to be more precise, weighting--with size. Some of the components in your image are not squares at all, but rather rectangles ("squares" per se being a subset of rectangles, of course).... So are you really looking for the size of squares, or the area of rectangles? or the sum of....? I trust you see the potential confusion here: these observations and questions are all asked in good faith. I'd really like to help, but, no, those images just raised more questions than the resolved.

mathetes Hi Sorry for the confusion! Attached is better image I hope that explains things! The small squares will have a standard weighting of 1. and then the larger ones will have a value consisting of how many squares would fit in it. i.e the weighting 4 square could fit 4 smaller squares in. I've listed how each column and row is used. Based on these i think a weighting could be calculated using a formula. I think it would work similar to: find the difference between the columns and if it is not 1 then the weight must be more than 1 but then the rows come in to determine how big it is? I hope that's clearer. Many thanks!

jacksaxby Sigh. I know it's clear to you. And I'm starting to see the pattern here. But, what's it all about? What I don't see is what you're trying to get to, first, with the calculations themselves.Then, in the bigger picture, what's the ultimate goal or objective? It might help too to know what the larger context is for even trying to do this in the first place. Are you in school, either as a student or teacher? Is this an assignment having to do withhow many two dimensional rectangle/squares of varying dimensions can be fitted into a square of X dimensions ORhow large a square will be needed to contain (with no wasted space) the maximum number of objects defined as.....OR something else altogether.Or are you an urban planner trying to figure out how many tables can be fit in a city square so the neighborhood restaurants can safely serve food to groups of varying sizes during the Covid-19 crisis?

mathetes Hi Sorry that I'm not making this clear Just trying to get the size of each box. without manually having to enter the weighting.# Image attached. Currently i enter the size of each one and it is very time consuming!

jacksaxby I'm sorry I'm so dense. We're clearly speaking past each other. I asked a short while ago for a worked out example. Here's the way I put it at the time, and the key there is "words that explain...what you expect the result to be"Better would be words that explain with an example or two what you expect the result to be for several of those sets of figures. You keep sending more images, which do nothing for me. (I'm a more verbal, text-driven animal, I guess). To the point that I don't even understand (apparently) what you mean by "get the size of each box" if it's not just 1x1=1; 3x1=3; 5x1=5If that's all it is, if you're really just asking how to do simple multiplication in Excel, then, in Column C, row 2, enter =A2*B2 and copy that formula and paste it in rows 3 through to the bottom row. If, on the other hand, you're looking for something else, what is that "something else"? It might be just as simple as spelling out what the "weighting" is that you're currently entering manually. And where do you get it from? How does it differ from size, or is it the same thing. As I said earlier, you seem to be using size and weighting almost interchangeably. If you have a spreadsheet going here, it actually would be helpful to post it rather than images. It would give me (or somebody else) a glimpse into what you're working with and what you've tried. I apologize for being so dense. As I said, we seem to be speaking past each other, each of us thinking we're being clear.

Calculate the width of square dynamically based on columns and rows

11 Replies

mathetes
Silver Contributor
Aug 06, 2020
jacksaxby

Well, there is a saying that "a picture is worth a thousand words," but, sadly, it's not always true. At least not when it comes to creating Excel formulas.

Better would be words that explain with an example or two what you expect the result to be for several of those sets of figures.

There's also a bit of potential confusion (at least in my mind) around whether there's a distinction between size and weight. The way you write, "a formula which determines the size (Weighting)" seems to equate weight--or, to be more precise, weighting--with size.

Some of the components in your image are not squares at all, but rather rectangles ("squares" per se being a subset of rectangles, of course)....

So are you really looking for the size of squares, or the area of rectangles? or the sum of....?

I trust you see the potential confusion here: these observations and questions are all asked in good faith. I'd really like to help, but, no, those images just raised more questions than the resolved.
- jacksaxby
  Copper Contributor
  Aug 06, 2020
  mathetes Hi Sorry for the confusion!
  
  Attached is better image I hope that explains things!
  
  The small squares will have a standard weighting of 1. and then the larger ones will have a value consisting of how many squares would fit in it. i.e the weighting 4 square could fit 4 smaller squares in.
  
  I've listed how each column and row is used. Based on these i think a weighting could be calculated using a formula.
  
  I think it would work similar to: find the difference between the columns and if it is not 1 then the weight must be more than 1 but then the rows come in to determine how big it is?
  
  I hope that's clearer.
  
  Many thanks!
  - mathetes
    Silver Contributor
    Aug 06, 2020
    jacksaxby
    
    Sigh. I know it's clear to you. And I'm starting to see the pattern here. But, what's it all about?
    
    What I don't see is what you're trying to get to, first, with the calculations themselves.
    Then, in the bigger picture, what's the ultimate goal or objective?
    
    It might help too to know what the larger context is for even trying to do this in the first place. Are you in school, either as a student or teacher? Is this an assignment having to do with
    how many two dimensional rectangle/squares of varying dimensions can be fitted into a square of X dimensions OR
    how large a square will be needed to contain (with no wasted space) the maximum number of objects defined as.....
    OR something else altogether.
    Or are you an urban planner trying to figure out how many tables can be fit in a city square so the neighborhood restaurants can safely serve food to groups of varying sizes during the Covid-19 crisis?

Forum Discussion

Calculate the width of square dynamically based on columns and rows

11 Replies

Resources