Home Page
   Articles
       links
About Us    
Traders        
Recipes            
Latest Articles
Excel Spreadsheet help
Page Previous  1, 2
 
Post new topic   Reply to topic    Downsizer Forum Index -> IT Matters
Author 
 Message
Rob R



Joined: 28 Oct 2004
Posts: 31902
Location: York
PostPosted: Tue Mar 18, 14 1:26 am    Post subject: Reply with quote
    

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
PostPosted: Tue Mar 18, 14 3:54 pm    Post subject: Reply with quote
    

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
PostPosted: Tue Mar 18, 14 4:00 pm    Post subject: Reply with quote
    

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
PostPosted: Tue Mar 18, 14 4:10 pm    Post subject: Reply with quote
    

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
PostPosted: Tue Mar 18, 14 4:21 pm    Post subject: Reply with quote
    

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
PostPosted: Tue Mar 18, 14 4:25 pm    Post subject: Reply with quote
    

How many ranges do you have?

Rob R



Joined: 28 Oct 2004
Posts: 31902
Location: York
PostPosted: Tue Mar 18, 14 4:46 pm    Post subject: Reply with quote
    

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
PostPosted: Tue Mar 18, 14 4:59 pm    Post subject: Reply with quote
    

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
PostPosted: Tue Mar 18, 14 5:03 pm    Post subject: Reply with quote
    

Ahh...

chez



Joined: 13 Aug 2006
Posts: 35934
Location: The Hive of the Uberbee, Quantock Hills, Somerset
PostPosted: Tue Mar 18, 14 5:11 pm    Post subject: Reply with quote
    

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
PostPosted: Tue Mar 18, 14 6:54 pm    Post subject: Reply with quote
    

If they do I'll be able to afford a secretary

Shane



Joined: 31 Oct 2005
Posts: 3467
Location: Doha. Is hot.
PostPosted: Wed Mar 19, 14 8:27 am    Post subject: Reply with quote
    

Can't you just use =2*CEILING(A1,20) ?

Rob R



Joined: 28 Oct 2004
Posts: 31902
Location: York
PostPosted: Wed Mar 19, 14 6:11 pm    Post subject: Reply with quote
    

Shane wrote:
Can't you just use =2*CEILING(A1,20) ?


Maybe...


Anyhow, is there a formula to tell the number of months between two dates?

Shane



Joined: 31 Oct 2005
Posts: 3467
Location: Doha. Is hot.
PostPosted: Thu Mar 20, 14 6:41 am    Post subject: Reply with quote
    

Assuming you're not dealing with timespans of several millenia, you could just use something like:

=INT((date2-date1)/30)

That will just round up or down to the nearest integer value. If you want to always round up or round down to the nearest number of months, use

=ROUNDUP((date2-date1)/30,0)

or

=ROUNDDOWN((date2-date1)/30,0)

If you're dealing with mahoosive periods of time, you'll want to replace the "30" with a "365/30", otherwise the margin of error will catch you out.

date1 and date2 should be the cells containing the start date and end date respectively.

Rob R



Joined: 28 Oct 2004
Posts: 31902
Location: York
PostPosted: Thu Mar 20, 14 11:16 am    Post subject: Reply with quote
    

Thanks - I was going to try that, but it turns out that Excel is absolutely useless these days and has wiped all the work I did yesterday, even though I saved it many times

Post new topic   Reply to topic    Downsizer Forum Index -> IT Matters All times are GMT
Page Previous  1, 2
Page 2 of 2
View Latest Posts View Latest Posts

 

Archive
Powered by php-BB © 2001, 2005 php-BB Group
Style by marsjupiter.com, released under GNU (GNU/GPL) license.
Copyright © 2004 marsjupiter.com