Excel Spreadsheet help

Latest Articles

Excel Spreadsheet help
Page 1, 2 Next

Downsizer Forum Index -> IT Matters

Author

Message

Rob R

Joined: 28 Oct 2004
Posts: 31902
Location: York

Posted: Mon Mar 17, 14 5:01 pm Post subject: Excel Spreadsheet help

Rather than consult the interweb, I thought I'd ask here first for a quick answer...

I have a group of values in a column for which I need a corresponding number to be displayed in the next column. I am sure there is a single function I can use to give the correct value from a possibility of ranges, but I can't remember which one. A simplified example is;

1-10 = 20
11-20 = 40
21-30 = 60
31-40 = 80
41-50 = 100

Thanks in anticipation

chez

Joined: 13 Aug 2006
Posts: 35934
Location: The Hive of the Uberbee, Quantock Hills, Somerset

Posted: Mon Mar 17, 14 5:04 pm Post subject:
The sum is twice the largest number. Is that what you're after, because that would be x2? Or am I missing something?

Rob R

Joined: 28 Oct 2004
Posts: 31902
Location: York

Posted: Mon Mar 17, 14 5:27 pm Post subject:
That's just an example, simplified for the purpose of illustration. The ranges might not be even, even.

chez

Joined: 13 Aug 2006
Posts: 35934
Location: The Hive of the Uberbee, Quantock Hills, Somerset

Posted: Mon Mar 17, 14 7:25 pm Post subject:
So, if it's in one range, then you need x displayed and another range, y displayed, and so on? I think you need if-then statements, separated by commas.

Rob R

Joined: 28 Oct 2004
Posts: 31902
Location: York

Posted: Tue Mar 18, 14 1:26 am Post subject:

Chez wrote:

So, if it's in one range, then you need x displayed and another range, y displayed, and so on?

I think you need if-then statements, separated by commas.

Yes.

And thanks, I think that's what I was suspecting, but it made my head spin setting the ranges within the 'IF's

anoia

Joined: 14 Nov 2010
Posts: 47
Location: Hertfordshire

Posted: Tue Mar 18, 14 3:54 pm Post subject:
I think VLOOKUP or HLOOKUP might work. You have a separate section with the values you want returned for each number: 1 20 2 20 3 20 and so on down to 49 100 50 100 Can't remember the exact details of syntax and not on a computer with decent excel to check, sorry!

Rob R

Joined: 28 Oct 2004
Posts: 31902
Location: York

Posted: Tue Mar 18, 14 4:00 pm Post subject:
Thanks - it's still making my head spin, I can figure out how to get it to look up a specific number, but not one within a range. I thought about rounding up the number and then using LOOKUP but rounding only seems to work for decimals, rather than whole numbers. It might be just as easy to do it manually...

chez

Joined: 13 Aug 2006
Posts: 35934
Location: The Hive of the Uberbee, Quantock Hills, Somerset

Posted: Tue Mar 18, 14 4:10 pm Post subject:
I think you need to do =(IF(A1>x),(A1<y),z) Which I think means that if A1 is more than x and less than y, the return is z. However, you will need a lot of IF statements - one for each of your ranges. What are you trying to do, in real world terms?

Rob R

Joined: 28 Oct 2004
Posts: 31902
Location: York

Posted: Tue Mar 18, 14 4:21 pm Post subject:
I have a number of weights, and I want to return the number of animals in each weight range and then cross reference that with various different formulas, such as feeding, dosing, density of animals in kg/sq m etc. The thing is, I used to be able to do this easily, 15 years ago

chez

Joined: 13 Aug 2006
Posts: 35934
Location: The Hive of the Uberbee, Quantock Hills, Somerset

Posted: Tue Mar 18, 14 4:25 pm Post subject:
How many ranges do you have?

Rob R

Joined: 28 Oct 2004
Posts: 31902
Location: York

Posted: Tue Mar 18, 14 4:46 pm Post subject:
11, at the moment, in 50kg increments, although potentially I could be looking at vary numbers of increments.

chez

Joined: 13 Aug 2006
Posts: 35934
Location: The Hive of the Uberbee, Quantock Hills, Somerset

Posted: Tue Mar 18, 14 4:59 pm Post subject:
I'd have 12 columns, then, with your cow-weights entered in COL1. The others are populated by TRUE/FALSE thingies, one range per column, that check to see if COL1 is within their range. You'll end up with 1x true and 10x false per cow and you can add up the columns to work out how many are in each range.

Rob R

Joined: 28 Oct 2004
Posts: 31902
Location: York

Posted: Tue Mar 18, 14 5:03 pm Post subject:
Ahh...

chez

Joined: 13 Aug 2006
Posts: 35934
Location: The Hive of the Uberbee, Quantock Hills, Somerset

Posted: Tue Mar 18, 14 5:11 pm Post subject:
This is assuming your cows are not going to get so fat that you need a millions columns ...

Rob R

Joined: 28 Oct 2004
Posts: 31902
Location: York

Posted: Tue Mar 18, 14 6:54 pm Post subject:
If they do I'll be able to afford a secretary

	Downsizer Forum Index -> IT Matters	All times are GMT Page 1, 2 Next
Page 1 of 2

View Latest Posts