I am a 61 year old beginner !!!

I need help to write a formula to get the following result.

B1=5 then C1 should read 1
B1=5.5 then C1 should read 1.5
B1=6 then C1 should read 2
B1=6.5 then C1 should read 2.5
B1=7 then C1 should read 3
B1=8 then C1 should read 2
B1=8.2 then C1 should read 2.2
B1=8.3 then C1 should read 2.3
B1=8.5 then C1 should read 2.5
B1=9 then C1 should read 3
B1=10 then C1 should read 4

I hope someone could help me out.

Thanks

pothgulla


--
pothgulla
------------------------------------------------------------------------
pothgulla's Profile: http://www.excelforum.com/member.php...o&userid=26431
View this thread: http://www.excelforum.com/showthread...hreadid=397077

If you set up a table, say in J2:K12, like this:

{5,1;5.5,1.5;6,2;6.5,2.5;7,3;8,2;8.2,2.2;8.3,2.3;8 .5,2.5;9,3;10,4}

where J2 is 5, K2 1, J3 5.5, K3 1, etc., you could use a lookup formula
in C1:

=VLOOKUP(B1,$J$2:$K$12,2,0)

where B1 houses a value like 6.5.

When B1 is 6.6, the formula will yield #N/A, an error.

If you use the following formula in C1:

=VLOOKUP(B1,$J$2:$K$12,2,1)

the result would be 2 with B1 = 6.6.

pothgulla wrote:
I am a 61 year old beginner !!!

I need help to write a formula to get the following result.

B1=5 then C1 should read 1
B1=5.5 then C1 should read 1.5
B1=6 then C1 should read 2
B1=6.5 then C1 should read 2.5
B1=7 then C1 should read 3
B1=8 then C1 should read 2
B1=8.2 then C1 should read 2.2
B1=8.3 then C1 should read 2.3
B1=8.5 then C1 should read 2.5
B1=9 then C1 should read 3
B1=10 then C1 should read 4

I hope someone could help me out.

Thanks

pothgulla



--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

pothgulla Wrote:
I am a 61 year old beginner !!!

I need help to write a formula to get the following result.

B1=5 then C1 should read 1
B1=5.5 then C1 should read 1.5
B1=6 then C1 should read 2
B1=6.5 then C1 should read 2.5
B1=7 then C1 should read 3
B1=8 then C1 should read 2
B1=8.2 then C1 should read 2.2
B1=8.3 then C1 should read 2.3
B1=8.5 then C1 should read 2.5
B1=9 then C1 should read 3
B1=10 then C1 should read 4

I hope someone could help me out.

Thanks

pothgulla

Construct a table like so:

1. Enter the first set of numbers (5, 5.5, 6 ... 10) starting in Cell
D1, going down until Cell D11.

2. Enter the second set of numbers (1, 1.5, 2 ... 4) starting in Cell
E1, going down until Cell E11

Enter this formula in Cell C1:

=IF(B1=\"\",\"\",VLOOKUP(C1,D1:E11,2,0))


Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=397077

pothgulla wrote in
:


I am a 61 year old beginner !!!

And I'm even extinct!

I need help to write a formula to get the following result.

B1=5 then C1 should read 1
B1=5.5 then C1 should read 1.5
B1=6 then C1 should read 2
B1=6.5 then C1 should read 2.5
B1=7 then C1 should read 3
B1=8 then C1 should read 2
B1=8.2 then C1 should read 2.2
B1=8.3 then C1 should read 2.3
B1=8.5 then C1 should read 2.5
B1=9 then C1 should read 3
B1=10 then C1 should read 4


Formula in C1:

=IF(B1<8,B1-4,B1-6)


--

It is I, DeauDeau
(Free after monsieur Leclerc in 'Allo, 'allo)

Thank you very much.

I am very grateful for all the help. I truly am beginner and I am
trying to make an excel spreadsheet. I do this mostly by trial and
error as I like to understand the programme.

Thanks

pothgulla


--
pothgulla
------------------------------------------------------------------------
pothgulla's Profile: http://www.excelforum.com/member.php...o&userid=26431
View this thread: http://www.excelforum.com/showthread...hreadid=397077