Calculate the width of square dynamically based on columns and rows

Copper Contributor
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!
11 Replies

@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 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?

@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=5

If 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.

@mathetes Hi

 

Yeah I have been using size/weighting interchangeably. So as you can see from the slots image. In the context the bigger games have a higher size value. The smaller squares have a base value of 1, these values have been input manually so far. We need to calculate the size value of each square using the columns and rows. There is 6 columns in this example so 6, 1 sized squares could fit on 1 row. a square than spans multiple columns is counted as 1 column but a larger weighting is assigned.

 

Attached another image, ive split it further. Thanks for the help!

 

I want to get the size of each. You can see that the 1st game starts on the 1st row at column 1, then the second game starts at column 3. this means that the size of game 1 will have a size of 2. However if the game spans 2 rows then this size will be 4. Thanks Again!

@jacksaxby 

 

Wait a minute here. Are you going FROM the images TO the numbers? TO the calculation?! Not the other way around?

 

Your raw material is the image? It wasn't a way to illustrate what you were tying to accomplish with the formulas, but a way to get to the numbers?

 

No wonder we weren't speaking the same language.  I was totally reading all of this as CREATING the squares and rectangles from the numbers in Excel's various columns.

 

I have no idea how to take a set of images (or one image with multiple rectangles and squares) and convert that into the row and column data you're asking for, thence to predict that weighting. Sorry. The other way around, yes, maybe we could talk.

 

If those images are discrete images, then it might be possible to extract pixel counts of length and width and start doing some things with that data, but that aside, I am afraid I don't know what you could do other than enter the numbers you've been entering, with whatever the heuristic is that you've developed to develop those numbers consistently. You need quantitative data of some kind to begin with; pure images, without some way to measure them via the computer...would not work. If you could take that image and get pixel counts for the horizontal and vertical axes, maybe that could serve as a starting point, but you'd still have to enter (Manually) how many images there are in each horizontal and vertical section....

 

Maybe there's somebody else out there, another expert in Excel, who knows a bit more about image manipulation, who can help. I like to think that at least I helped us get to the bottom of the confusion.

 

Best wishes.

@mathetes Hi, I manually input the correct row and column the images are on. its just using a formula on the rows and columns to get the size of it.

 

For instance.  =IF((A3-A2)>1,A3-A2,1)  gets me the correct columns but does not account for the size of the games that span multiple rows

@jacksaxby 

 

Since nobody else has chimed in (yet), I'll give it another try. I still need to give you a more clear explanation to someone who's not inside your head. You know what you're saying, what you're doing. It's still not clear to me.

 

I've asked before, and I'll ask once more:

 

Walk me through how you'd do those calculations that you want if you were NOT trying to write it in Excel. That's what I meant in one of my earlier messages when I asked you to walk through a complete example for (I said then several rows because I was going the other direction) ..

So now, I'm asking that you walk through .for several images and spell out what you're doing to get the numbers you are looking for. Don't talk about rows and columns; talk about numbers and where you get them from, what you do with them, how you get the size/weight from those.

 

Maybe another way to say it: if you were back in school working this out on a blackboard (or whiteboard), how would you do it and explain it to the others in the class?

@mathetes 

 

 

 

Attached is an excel format. hopefully this better explains it.

 

Thanks for trying

@jacksaxby 

 

I've given you a few thoughts on the attached. But bottom line, I'm not sure that this can be done dynamically, as your original request seems to be seeking. I'd be delighted to see others chime in with suggestions.......

 

Are you really doing this with the kinds of images you've shown? Why is it important? Part of some game?