Excel Help Required !!

[markwales]

This should be VERY simple, but it's driving me insane.

If Cell A1 = 100
If Cell B1 = 100

If column A is increased to 101, I need column B to decrease by the same amount that A has increased.

Basically I need one cell to go down, when the other increases, and similarly if cell a1 is decreased I need B1 to increase. B needs to do the opposite of A.

The higher A is, the lower B is.

Any takers?

That sounds confusing to me when reading it back!!

[glynners]

Is the starting point always 100? or was that just an example?

[glynners]

in cell b1 try "=(starting value-a1)+starting value"

eg starting value = 100 would be = (100-A1)+100

[vegyjones]

B1 = 100-(a1-100)

[glynners]

both work same, but by answering 5 min earlier i win!!! :wiggle: :D :wiggle:

[markwales]

A colleague has sorted me out with the following

=B1-(A1-B1)

Works a treat :D

Thanks guys

[andyp]

Vegy you snooze,
you loose :D

:snooze

[vegyjones]

Vegy you snooze,
you loose :D

:snooze
You LOSE for inaccurate spelling! :uoyurs :D

[markwales]

In fact it doesn't work :yikes:

The control value is 120

So if,

Cell A = 120
Cell B = 65

If cell A increases to 121, Cell B needs to decrease to 64

HELP !!! Will try all your above answers shortly

[andyp]

:dunce :D

It is actually a new saying Vegy! your loose means your heads not screwed on!? (well thats my story and im sticking to it) :D

Get with the street talk mate. innit

[vegyjones]

B1 = 65-(a1-120)

[glynners]

in cell b1 try "=(starting value-a1)+starting value"

eg starting value = 100 would be = (100-A1)+100


this definately works tested with loadsa different values

[vegyjones]

:dunce :D

It is actually a new saying Vegy! your loose means your heads not screwed on!? (well thats my story and im sticking to it) :D

Get with the street talk mate. innit
Bein' a Landunner!

I ain' ah all familiar wiv North speak!

I'm off nah... dahn ta Maagit! :D

[vegyjones]

this definately works tested with loadsa different values
I win for being 2 minutes quicker than Glynners second time round! :D

[andyp]

this definately works tested with loadsa different values


OOH, u gettin bitchy glynners? SCRAP SCRAP SCRAP, FIGHT FIGHT FIGHT :punch2

[markwales]

Right, I'll try to explain again !!! I think I've messed up the explanation a bit :yikes:

Cell A = 120 (variable figure)
Cell B = 65 (Control Figure)
Cell C = whatever Cell A increases by, Cell B minus that figure.

So if Cell A is increased to 121, Cell C will be 64
If cell A is decreased to 119, Cell C will be 66

I think I might have got me control figures wrong earlier :D

[glynners]

Shut it andy or i'll throw my stapler at ya :uoyurs :)

[vegyjones]

C = B-(a-120)

This calculation will devrease or increase B1 by whatever A1 increases or decreses by!

[vegyjones]

C = B-(A-120)

is definitely what you are looking for :wink

[andyp]

C = B-(a-120)

This calculation will devrease or increase B1 by whatever A1 increases or decreses by!

Vegy, i can see two spelling mistakes... gotcha :uoyurs :D

[markwales]

Super Dooper Veg....Racing Sim sectional timings have just been born !!! :D:D:D

[vegyjones]

Vegy, i can see two spelling mistakes... gotcha :uoyurs :D
:D

[vegyjones]

Super Dooper Veg....Racing Sim sectional timings have just been born !!! :D:D:D
And it's my creation Ha, Ha, Ha, Ha Ha *cough* Ahem! :)

[markwales]

You'd have a heart attack if you saw all the other calcs sat behind it Veg !!! This was just the final nail

[glynners]

:doh

[vegyjones]

You'd have a heart attack if you saw all the other calcs sat behind it Veg !!! This was just the final nail
I am still recovering from Mat's calculation last week Ooo Ooon Ooo Ooo :geek

[markwales]

Right, let's make this a bit more complicated....

If Cell A is increased by 1
then Cell C is Cell B - 0.1 !!

Basicaslly Cell C moves up or down by 10% of what A moves by :D

This would be ideal if we could get this one to work.

[vegyjones]

Shouldn't be too hard!

C = B-((A-120) x 0.1)

That is now correct! :D

[sparkyminer]

Vegy, stop being intelligent, I can't handle it. :D

[glynners]

on fire today vegy, i bow to your knowledge! :clp

[andyp]

Vegy, watch yourself mate, Glyn will do anything to get on his knees in front of you! :icon_tong

[markwales]

Shouldn't be too hard!

C = B-((A-120) x 0.1)

That is now correct! :D

GEEEENIUS, even if you did miss out 1 set of all important brackets !!!

I now have it fully working :D

[vegyjones]

GEEEENIUS, even if you did miss out 1 set of all important brackets !!!

I now have it fully working :D
What brackets is that then...

I can get some from Woolworths! :D

[mathare]

I am still recovering from Mat's calculation last week Ooo Ooon Ooo Ooo :geekYou should have seen the VBA I wrote last night then.

In-cell formulae are for wimps :)

[vegyjones]

In-cell formulae are for wimps :)
Alright clever cloggs!

You may be clever at Excel...

but it took you over 10 minutes to write that 2 line reply! :laugh

[mathare]

but it took you over 10 minutes to write that 2 line reply! :laughThat's true - my boss reappeared! :yikes:

[vegyjones]

That's true - my boss reappeared! :yikes:
:giggle

[andyp]

That's true - my boss reappeared! :yikes:

Mat, do you work as a magician? :doh :D