Excel Formula help

P

pothgulla

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
 
B

BenjieLop

pothgulla said:
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
Click to expand...

Construct a table like so:

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

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

Enter this formula in Cell C1:

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


Regards
 
A

Aladin Akyurek

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.
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
Click to expand...

--

[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.
 
D

Dodo

I am a 61 year old beginner !!!
Click to expand...

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
Click to expand...

Formula in C1:

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

pothgulla

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
 
Top