New to excel - need help with a formula

[kessa]

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

[Pete_UK]

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

[JE McGimpsey]

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"

[JE McGimpsey]

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.

[kessa]

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

[JE McGimpsey]

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?

[kessa]

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

[kessa]

Hi All,

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

Thanks
Kessa

[Pete_UK]

Both formulae are expecting numbers in B19 and in '4th November
2005'!B19, as one is subtracted from the other - are these your
"position" columns?

If you have text values in them, such as "Not in top 100" or just
spaces, then you will get the #VALUE! error because you cannot perform
arithmetic on text.

The solution is to ensure that you have only numbers or blanks
(completely empty) in these cells.

Pete

[kessa]

Hi Pete_UK,

A quck update - I just managed to get your example to work (thanks!) and so I'm guessing I had popped it in (or referenced) a field where one of the cells contained "Not in top 100" - doh!

In the cases of the "Not in top 100" entries, I really need to leave them in and so I wondered if excel offered some kind of replace function? For example, would it be possible to "replace" the occurances of "Not in top 100" (in the formula only) with the number "101"?

Therfore the logic would go something like this:

If the appropiate cell position column is equal to "Not in top 100" then replace with "101" then perform calculation to determine the difference.
(This would obviously need to be incorporated into your existing formula)

I hope that makes sense?

Also, it it possible to reference the "up", "down" and "same" image arrows in excel?

Thanks again for all of your help!
Kessa

[Pete_UK]

If the text "Not in top 100" could appear in either B19 or in '4th
November 2005'!B19, then you could amend my earlier formula as follows:

=IF(OR(B19="Not in top 100", '4th November 2005'!B19="Not in top
100"),"Outside top 100", 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")&")" )))))

Watch out for line breaks in the posting - this is all one formula. You
can change the comment "Outside top 100" how you wish.

Here's one way of achieving the images - assuming the formula above is
in C19, enter this formula in D19:

=IF(LEFT(C19,1)="U",CHAR(74),IF(LEFT(C19,1)="D",CHAR(76),IF(LEFT(C19,1)="S",CHAR(75),"")))

and format the cell using the Wingdings font - these are smilies in
Wingdings.

Hope this helps.

Pete

[kessa]

Cheers Pete,

Actually, just after posting my last message I had another go and tried using IF. I came up with the following all on my little lonesome (I just wanted to let you know that all your teaching is not in vain! ):

=IF('4th November 2005'!B16="","nothing here dude",
IF(B16<'4th November 2005'!B16,"UP (+"&TEXT(IF('4th November 2005'!B16="Not in top 100","101",'4th November 2005'!B16)-IF(B16="Not in top 100","101",B16),"00")&")",
IF(B16='4th November 2005'!B16,"Same",
IF(B16>'4th November 2005'!B16,"DOWN (-"&TEXT(IF(B16="Not in top 100","101",B16)-IF('4th November 2005'!B16="Not in top 100","101",'4th November 2005'!B16),"00")&")" ))))

Let me know if you can spot any errors or if your solution provides a neater alterative - I just was quite pleased to have managed to figure something out

Regarding the images, is the only option to use wingdings, as I really wanted to be able to use a green arrow for up and a red arrow for down (unless you can colour wingdings?)

Cheers
Kessa

[Pete_UK]

Kessa,

well done - the best way to learn is to try things and then to learn
from any mistakes. You've chosen to replace "Not in top 100" with "101"
which is still text and a bit arbitrary, whereas my formula returns a
message. I think you will need to change "101" to 101 for it to work.
Then test it out by putting different values in the cells referred to.

Regarding the images, you can use any font and a character in that font
which is displayed as an arrow - you may well have another symbol font
on your PC which you could use.

You can use conditional formatting to give you different coloured
effects depending on a cell's contents - select the cell(s) and click
Format | Conditional Formatting. In the panel presented to you select
Cell Contents then "Equal to" and then "J" (same as character 74 that I
recommended above). Then click on the Format button, and then choose
colour green, maybe with a background colour (Patterns tab) of black.
Then click ok and you want to "Add" another condition and choose a
different colour and background in the same way - maybe red foreground
with yellow background. You can have a maximum of 3 conditions.

Hope this helps.

Pete

[Pete_UK]

Kessa,

I've experimented a bit and found the following arrow symbols in
Wingdings (I think it depends on which version of Windows you have, as
a font sheet I did some years ago is different):

UP - CHAR(199), Down - CHAR(200), Same - CHAR(198)

My other comments about conditional formatting still hold, but you will
have to enter the CHAR(199) rather than just "J".

Hope this helps.

Pete

[kessa]

Hi Pete_UK,

Thanks - the arrows are now working a treat. The only thing I still can't get to work is the conditional formatting.

I've tried using: "cell is" ---> "equal to" --->"CHAR(199)"
(OR, CHAR(200), CHAR(198) as appropriate)

....but the colour remains the same.

I also tried using "greater than", "less than" (as an attempt at error checking) and that did colour the arrows, which I guess indicates it doesn't recognise the value....?

Any ideas?
Cheers
Kessa

[Pete_UK]

Hi Kessa,

I think if you highlight the cells again and go to Format | Conditional
Format you will see that Excel will have put quotes around the
char(199) etc, i.e. it will look like:

="char(199)"

in the panel. All you need to do is edit this to remove the quotes in
both panels (red and green), so that it looks like:

=char(199)

and then click OK. You should then see your red and green arrows, as
appropriate.

Hope this helps.

Pete

[Pete_UK]

Kessa,

As an aside, but related to your task, enter this formula in A1 of a
blank sheet:

=CHAR(ROW())

then copy down to row 255. Highlight this column and choose Wingdings
as the font. You can now scroll down and see what symbols are available
with this font. You might like to choose characters 232 to 234 for your
arrows, or characters 241 to 243, or even 216 to 218.

Pete

[David McRitchie]

additional on the aside comment:
You could extend the example with other special fonts as well
http://www.mvps.org/dmcritchie/rexx/htm/fonts.htm
and can be used as it appears there if you are using Internet Explorer
as your browser.

"Pete_UK" <[email protected]> wrote ...
> Kessa,
>
> As an aside, but related to your task, enter this formula in A1 of a
> blank sheet: =CHAR(ROW())
>
> then copy down to row 255. Highlight this column and choose Wingdings
> as the font. You can now scroll down and see what symbols are available
> with this font. You might like to choose characters 232 to 234 for your
> arrows, or characters 241 to 243, or even 216 to 218.

[kessa]

Hi Pete_UK,

Yippee!!! That's perfect - thank you SO much!
(...and thanks for the extra info about viewing the charset for the wingdings font)

But finally, thanks so much for your continued patience and support - I've already learnt so much more about excel than I could have originally expected.

Cheers!

David McRitchie - Hi David,

Thanks for the extra info. It's a really handy to be able to see the various symbols side by side.

Cheers both!
Kessa

[Pete_UK]

Hi Kessa,

thanks for feeding back. If you have any other queries in the future,
you know where to come to ...

Pete