Excel formula Writing help

[Letz]

Originally Posted by 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

Good call
With the function wizard (insert function) it is really very simple
It guides you as to what to put where and minimises formulae error
Best of luck

[colinicky]

I'd like to do something along the lines of :-


-----A --------------- B-----------C--------D
Groundrent --------2350-------2000----350

or I might choose


Gas ------------------48---------2.40----45.60


sort of like a drop down list & select which I want & it all goes were it should
Is this what vlookup can do ?

[big_si]

You can do dropdown lists by making the list you want. Then clicking on the cell where you want the choice to be and click on the data menu, then validation. In here you can select "list" and then the range of cells which contain the choices you want to give.


This can then be combined with other formulae...

[Letz]

Originally Posted by colinicky

I'd like to do something along the lines of :-


-----A --------------- B-----------C--------D
Groundrent --------2350-------2000----350

or I might choose


Gas ------------------48---------2.40----45.60


sort of like a drop down list & select which I want & it all goes were it should
Is this what vlookup can do ?

That's the sort of thing
There are other functions similar to Vlookup, like Hlookup, and you may want to have a read and a play
Go to insert, function and select the functions for "lookup and reference" in the category drop down menu
I think you'll find hours of fun

What you might also want to play with is setting up some pop up/input screens and short macros
It's ages since I built tables with those things so it can only have got easier. I just ran macros and then looked at the language and fiddled about

If you want to start playing with macros you can do "record" then perform a simple task, like "copy from to...." then you stop the macro and, once assigned to a button or key stroke, you have a simplified function
If you find you like it, get a book of codes and you're away. Pretty sure it was visicalc when I did it but that was about 10 years ago !
Someone will come along who's more uptodate than me

[Janos]

Originally Posted by colinicky

Ok thanks to Mark Chase in my previous thread I think I am nearly there
But I need help with a formula I need something to work the following :-

If F5 = 0 then H5 = F5 "OR" If F5 = 1 then H5 = F5/1.175 "OR" If F5 = 2 then H5 = F5/1.05 "OR" If F5 = 4 then H5 = F5/407*7

Can somebody please help
TIA

Nested IFs work perfectly fine, but can be hard to read. An alternative could be:

=CHOOSE(F5+1, F5, F5/1.175, F5/1.05, 0, F5/(407*7))

Or slightly more tidier.
=F5/CHOOSE(f5+1,1,1.175,1000,407*7)

(I have chosen dummy values (0 and 1000) for the case F5=3, which I think doesn't occur)

Excel's help is really good. Have a look at some of the worked examples.

Janos

[big_si]

It's vb now... and they no longer have the abacas attachment you plug in the back of the computer

[big_si]

Originally Posted by Janos

Nested IFs work perfectly fine, but can be hard to read. An alternative could be:

=CHOOSE(F5+1, F5, F5/1.175, F5/1.05, 0, F5/(407*7))

Or slightly more tidier.
=F5/CHOOSE(f5+1,1,1.175,1000,407*7)

(I have chosen dummy values (0 and 1000) for the case F5=3, which I think doesn't occur)

Excel's help is really good. Have a look at some of the worked examples.

Janos

Ooo, I've never stumbled across the CHOOSE function! That looks easy!




Bloody hell, does this thread count as the dullest thread on a diving forum!

[Letz]

Originally Posted by big_si

It's vb now... and they no longer have the abacas attachment you plug in the back of the computer

You talking to me ? LOL

Don't dis the abacus. Wouldn't have got anywhere without the abacus and even Babbage started there
And no! I'm not that old !!

[big_si]

Originally Posted by Sixsquid

You talking to me ? LOL

Don't dis the abacus. Wouldn't have got anywhere without the abacus and even Babbage started there
And no! I'm not that old !!

I can't see anyone else here. You must be talking to me!

[Letz]

Originally Posted by big_si

Bloody hell, does this thread count as the dullest thread on a diving forum!

LMAO

Originally Posted by big_si

I can't see anyone else here. You must be talking to me!

now I'm ............. RAOTFLMFAO