subtracting two numbers

**jippee** · 03-21-2010, 01:02 PM

ok its not as easy as it sounds, but what im trying to calculate is depending on a position of a song each week. Eg song : Kesha's Tik-Tok is currently at no. 8 and the next week its at 3. I want to calculate that for that song. but you see it will be in a different cell each
Please can you help me with a formula if its possible?

*it's not just 1 song i want to calculate its 40 different tracks each week. Here's a piece of the full chart

Current Position. Last week
1 (find diff btwn this no and that one) 4 Iyaz Replay
2 9 Pitbull ft Akon Shut it down
3 6 One Republic All the right moves
--------------

**zbor** · 03-21-2010, 01:41 PM

Can you upload example?

**martindwilson** · 03-21-2010, 03:27 PM

assuming this week iw in col a a2 downwards and last week is col b b2 down
then something along the lines of

=IF(B2-A2>0,"up "&B2-A2,IF(B2-A2<0,"down "&ABS(B2-A2),IF(B2=A2,"no change")))

**zbor** · 03-21-2010, 03:33 PM

or this

=CHOOSE(SIGN(A1-B1)+2;"up";"same";"down")

**martindwilson** · 03-21-2010, 03:42 PM

clever that, i've never really used sign and completely forgot it, nice one.

**jippee** · 03-22-2010, 05:31 AM

hey thanks for the info, listen each chart will either be on a separate sheet or on a new workbook, depending if its a new month.

Can you upload example?

Yeah but where do i upload it?

CHART 1/week 1
Current Position. Last week
*cell A1, sheet 1* 1 4 Iyaz - Replay

CHART 2/week 2
Current Position. Last week
*cell A7, Sheet 2* 7 1 Iyaz - Replay

i want to calculate the difference between week 1 position and week 2 position.
****those are estimates cell positions, each week the song position is going to be different, so i want a formula that can pick out the song name and use its own values, so the next week (week 3) might be in cell A13.

thanks

**zbor** · 03-22-2010, 05:36 AM

You can click New post and then click stample icon.

Did you try to implement solutions above?

(maybe you need to change ; to , in my example)

=CHOOSE(SIGN(A1-B1)+2,"up","same","down")

**jippee** · 03-22-2010, 05:46 AM

i have shown my example in my previous post^
thanks

=IF(B2-A2>0,"up "&B2-A2,IF(B2-A2<0,"down "&ABS(B2-A2),IF(B2=A2,"no change")))

=CHOOSE(SIGN(A1-B1)+2;"up";"same";"down")

thanks, like these ones to show the change, but will also like to show the amount in change, like moved up '+13' places or down '-5' places.

thank u!

**zbor** · 03-22-2010, 06:20 AM

It's showed example and we would rather see uploaded example

**martindwilson** · 03-22-2010, 09:37 AM

=IF(B2-A2>0,"up "&B2-A2,IF(B2-A2<0,"down "&ABS(B2-A2),IF(B2=A2,"no change")))
does that it gives back "up 13" or "down 4" or "no change"
if you want the +/- sign in there then

=IF(B2-A2>0,"up +"&B2-A2,IF(B2-A2<0,"down "&B2-A2,IF(B2=A2,"no change <>")))