New to excel - need help with a formula

Hi All,

I'm new to Excel ( and to this forum ) and so I hope somebody may be able to help me.

I've got 2 questions....

Question 1:
I've got a spreadsheet which takes data from one worksheet and uses it to calculate data in a second worksheet using the following code / formula:

=IF('4th November 2005'!B19="","nothing here dude",IF(B19<'4th November 2005'!B19,"UP",IF(B19='4th November 2005'!B19,"Same",IF(B19>'4th November 2005'!B19,"DOWN"))))

The problem is, when I create a new worksheet I have to go through and update all of the references to the previous worksheet. I know that I can do a "find and replace" (which isn't too much of a pain), but what I would like to do is use some kind of a relative (rather than absolute) formula.

So instead of saying "go to the worksheet called '4th November 2006' " I could instead say "go to the sheet which preceeds this one"

- hopefully that makes sense!

Question 2:
In addition to the above formula (which just tells me if a value is higher, lower, or the same as the previous value) is it possible to calculate what the difference is and display that in brackets?

For example:
If a result in the spreadsheet for "4th November 2005" = 6 and the result in the spreadsheet for "4th February 2006" = 1, it would show:
"UP (+5)"

Even better still, could I also reference an image (up arrow / down arrow) so that I could generate a result which looks a bity like those you get in the music charts.

Cheers!
Kessa

I'm not sure about your first question, but in answer to your second
question, you can amend your existing formula as follows:

=IF('4th November 2005'!B19="","nothing here dude",IF(B19<'4th November
2005'!B19,"UP (+"&TEXT('4th November
2005'!B19-B19,"00")&")",IF(B19='4th November
2005'!B19,"Same",IF(B19>'4th November 2005'!B19,"DOWN (-"&TEXT(B19-'4th
November 2005'!B19,"00")&")" ))))

Hope this helps.

Pete

This requires a User Defined Function (UDF). See

http://www.mcgimpsey.com/excel/udfs/prevsheet.html


In article <[email protected]>,
kessa <[email protected]> wrote:

> QUESTION 1:
> I've got a spreadsheet which takes data from one worksheet and uses it
> to calculate data in a second worksheet using the following code /
> formula:
>
> =IF('4th November 2005'!B19="","nothing here dude",IF(B19<'4th November
> 2005'!B19,"UP",IF(B19='4th November 2005'!B19,"Same",IF(B19>'4th
> November 2005'!B19,"DOWN"))))
>
> The problem is, when I create a new worksheet I have to go through and
> update all of the references to the previous worksheet. I know that I
> can do a "find and replace" (which isn't too much of a pain), but what
> I would like to do is use some kind of a relative (rather than
> absolute) formula.
>
> So instead of saying "go to the worksheet called '4th November 2006' "
> I could instead say "go to the sheet which preceeds this one"

One way:

=IF('4th November 2005'!B19="", "nothing here dude", CHOOSE(SIGN(B19
- '4th November 2005'!B19)+2, "UP", "SAME","DOWN") & TEXT(B19 - '4th
November 2005'!B19, " (-0); (+0);;"))

In article <[email protected]>,
kessa <[email protected]> wrote:

> QUESTION 2:
> In addition to the above formula (which just tells me if a value is
> higher, lower, or the same as the previous value) is it possible to
> calculate what the difference is and display that in brackets?
>
> _For_example:_
> If a result in the spreadsheet for "4th November 2005" = 6 and the
> result in the spreadsheet for "4th February 2006" = 1, it would show:
> "UP (+5)"
>
> Even better still, could I -also -reference an image (up arrow / down
> arrow) so that I could generate a result which looks a bity like those
> you get in the music charts.

Hi JE McGimpsey & Pete_UK,

Thank both for your help on this!

At the moment I seem to be getting a #value! error when I try either solution. Any ideas? Do I need to set something else up / change something?

To help me figure out what's going on, could you please let me know what the following are/do:

Choose
Sign
Text

Are they functions which Excel will recognise, or are they things that I need to set a UDF for?

Also, JE McGimpsey - thanks for the info about the UDF. I had no idea that you could declare your own functions in Excel.... how cool is that!

Cheers
Kessa

Hard to troubleshoot without knowing what the values are in the relevant
cells.

You can get a #VALUE! error if one of the cells contains text (or a
number entered as text). That includes space characters if you "delete"
cells by typing the space bar.

In article <[email protected]>,
kessa <[email protected]> wrote:

> At the moment I seem to be getting a #value! error when I try either
> solution. Any ideas? Do I need to set something else up / change
> something?

Hi JE McGimpsey,

The "position" column contains the following data:

• "Not in top 100"

• A number (from 1 - 100)

• Empy (whitespace for no value)

In addition, the column for used of calculating the difference (where the formula is located) contains / writes:

• Up

• Down

• Same

• Nothing here dude (which I use to spot errors, etc)

In all cases, the formatting has been set to "General" as I never know whether a particular field will contain letters, numbers or both.

Does that help at all?

Cheers
Kessa

Hi All,

Can anyone offer a follow up on this.... JE McGimpsey?

Thanks
Kessa