Excel formula Writing help

[Adrian Kelland]

Originally Posted by Sixsquid

Sorry Hard to explain here and I misunderstood the other question. Big Si's answer to that should work

I'll see if there's a way I can explain the lookup table better

Excel -- Worksheet Functions -- VLookup

[big_si]

Originally Posted by Sixsquid

Sorry Hard to explain here and I misunderstood the other question. Big Si's answer to that should work

I'll see if there's a way I can explain the lookup table better

Should? Oh ye of little faith!! :P

What's even more impressive is that I did it whilst multitasking and cooking a Looooovely roast pork dinner! (mmmmmm crackling!)

[colinicky]

Ok thats got that working
So I thought that I could be clever & write the following but it don't work !

=IF(F5=0,J5:S5*0,IF(F5=1,J5:S5*17.5%,IF(F5=2,J5:S5 *5%,IF(F5=4,J5:S5/407*7,"NOT VALID VALUE"))))


What have I got wrong please ?

[big_si]

Originally Posted by colinicky

Ok thats got that working
So I thought that I could be clever & write the following but it don't work !

=IF(F5=0,J5:S5*0,IF(F5=1,J5:S5*17.5%,IF(F5=2,J5:S5 *5%,IF(F5=4,J5:S5/407*7,"NOT VALID VALUE"))))


What have I got wrong please ?

Whatcha trying to do? Is is the same sort of thing as the original example but on multiple lines?

If so you can just put the formula in the top line and change all references of F5 to $F$5 then click in the box with the formula and drag it down the column and it will just copy down.

[Sixsquid]

Originally Posted by big_si

Should? Oh ye of little faith!! :P

What's even more impressive is that I did it whilst multitasking and cooking a Looooovely roast pork dinner! (mmmmmm crackling!)

Mmmm Yummy

Right, read that link
But...

If it's the basic principle that hasn't been explained enough I'll try again

Let's say you have 5 different sizes of cylinder :- 3, 7, 10, 12 & 15
Each of these have an associated price (for example)
You have a list like this
3 50
7 90
10 110
12 135
15 155

You want to be able to type the cylinder size into a excel cell and automatically retrieve the price

So, you have the Vlookup function in, say, cell A1 where the result will appear
You input the cylinder type in B1

You create a table like above
Select Insert, function, Vlookup and populate the little wizard table

eg


In this example I put 'a' in B1 so, as per the table the result (which you can't see due to me pulling up the wizard to show it - only the function is showing) is 10

Does that explain the principle, assuming we needed to start there?

[colinicky]

Takes the numbers from J5 to S5 adds them together & multiplys by 0 or 17.5% or 5% or by 7/407ths but I get errors

[Sixsquid]

Originally Posted by colinicky

Ok thats got that working
So I thought that I could be clever & write the following but it don't work !

=IF(F5=0,J5:S5*0,IF(F5=1,J5:S5*17.5%,IF(F5=2,J5:S5*5%,IF(F5=4,J5:S 5/407*7,"NOT VALID VALUE"))))


What have I got wrong please ?

If you are trying to do a sum there you need SumJ5.S5
If that's what you're trying to do, although multiplying anything by 0 will result in 0

[big_si]

Originally Posted by colinicky

Takes the numbers from J5 to S5 adds them together & multiplys by 0 or 17.5% or 5% or by 7/407ths but I get errors

Originally Posted by colinicky

Ok thats got that working
So I thought that I could be clever & write the following but it don't work !

=IF(F5=0,SUM(J5:S5)*0,IF(F5=1,SUM(J5:S5)*17.5%,IF( F5=2,SUM(J5:S5)*5%,IF(F5=4,SUM(J5:S5)/407*7,"NOT VALID VALUE"))))


What have I got wrong please ?

You were nearly there then. Just need to add SUM( : ) to the ranges you were looking to add together (as changed above).

[Adrian Kelland]

yep sum(j5:s5)

[colinicky]

Thanks people muchly appreciated Now to read up on Vlookup

Most of what we enter is repetition so I "think" it would be helpfull