I need help writing a formula

**dpston** · 06-16-2013, 12:42 PM

okay. i need help with a formula involving ranges. if i were to type in a number in d17, i need a correlating number to show up in f17. the ranges are 0-9= 6, 10-19=15, 20-29=19, 30-44=24, 45-59=27, 60-74=30 and 75 or more=34. also, if i type in dry hop into d17 i would like for f17 to show 0. so, if i were to type in 25 in d17, then i would like to see 19 in f17. i kind of have an idea on how to begin, it's just not working out. please, any help will be appreciated. thank you.

**Ace_XL** · 06-16-2013, 12:45 PM

Use

=LOOKUP(D17,{0,10,20,30,45,60,75},{10,15,19,24,27,30,34})

**dpston** · 06-16-2013, 01:10 PM

okay. i need it to be within a range. is there any way to set it from 0-9 and so forth?

**Tony Valko** · 06-16-2013, 02:12 PM

Create this 2 column table:

0.....6
10...15
20...19
30...24
45...27
60...30
75...34

Assume that table is in the range A2:B8.

Then, this formula entered in F17:

=IFERROR(LOOKUP(D17,A2:B8),0)

**Ace_XL** · 06-16-2013, 02:17 PM

......................

**Ace_XL** · 06-16-2013, 02:18 PM

via PM

let's say i typed in 0 in d17. then i would get 6 in f17. but, if i wanted to type in 5 in d17, i want 6 to show up as well. i want 0-9 to be 6, 10-19 to be 15. basically, this is how it looks. i would like a shortened version if at all possible:

=LOOKUP(D17,{"Dry Hop",0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75},{0,6,6,6,6,6,6,6,6,6,6,15,15,15,15,15,15,15,15,15,15,19,19,19,19,19,19,19,19,19,19,24,24,24,24,24,24,24,24,24,24,24,24,24,24,24,27,27,27,27,27,27,27,27,27,27,27,27,27,27,27,30,30,30,30,30,30,30,30,30,30,30,30,30,30,30,34})

Use

Please Login or Register  to view this content.

some reading on the LOOKUP function
http://support.microsoft.com/kb/324986

**dpston** · 06-16-2013, 02:58 PM

thank you very much. sorry for the confusion on my part. i see how it works. this is exactly what i wanted.

**FDibbins** · 06-16-2013, 03:08 PM

Hi and welcome to the forum

If you need further with your question, in accordance with forum rule, 1st please rename your thread to something more meaningful, that actually describes your problem.

Because thread titles are used in searching the forum it is vital they be written to accurately describe your thread content or overall objective using ONLY search friendly key words. That is, your title used as search terms would return relevant results.

Also, many members will look at a thread title, and if it is of interest to them, or it falls within their area of expertese, they might only open those threads.

Look at it this way...if you typed that title into google, what would you expect to get back?
To change a Title on your post, click EDIT on you're 1st post, then Go Advanced and change your title