assign values to different cell

jeffep

New Member
Joined
Oct 17, 2006
Messages
3
I've got a series of numbers and I'd like to assign a value to a different cell.

A1 14
B1 12
C1 19
D1 27

I'd like to write (somewhere):

if (A1> B1)
E1 = E1 + 3
if (C1 > D1)
E1 = E1 + 4

so I'm constantly changing the value in E1 based on a comparison of numbers in other cells. Is there a way in Excel to do this?

Thanks.

J
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Macropheliac

Board Regular
Joined
Aug 26, 2005
Messages
165
I don't know if you're looking for a formula or VBA solution, but the following VBA procedure should do it.
Code:
Sub Compare_Ranges()

If Range("A1").Value > Range("B1").Value Then
        Range("E1").Value = Range("E1").Value + 3
End If

If Range("C1").Value > Range("D1").Value Then
        Range("E1").Value = Range("E1").Value + 4
End If

End Sub

I'm sure someone else will supply a formula solution.

Mac
 

jeffep

New Member
Joined
Oct 17, 2006
Messages
3
Thanks for the quick reply. I'd prefer a formula version, as there will be dozens/hundreds of these comparisons and assignments. But it's nice to know there is a way to do it.
 

JamieDuncan

Board Regular
Joined
Aug 23, 2006
Messages
132

ADVERTISEMENT

what is the standard value of E1?
if you are inputing data into cell E1 you will not be able to place a formula there.
If the data in E1 is from another cell then it could be done.
Also if bother statements are true would E1 = E1+7?
 

JamieDuncan

Board Regular
Joined
Aug 23, 2006
Messages
132
Put this formula into E1 and it will equal F1

=IF(A1>B1,F1+3+(IF(C1>D1,4,0)),F1+(IF(C1>D1,4,0)))

if A1>B1 it will equal F1 + 3
if C1>D1 it will equal F1 + 4
if A1>B1 and C1>D1 it will equal F1 + 7
 

jeffep

New Member
Joined
Oct 17, 2006
Messages
3
I over-simplified in my original question. I've got a series of these numbers

A B C D E F G H
1 12 16 19 24 11 5 34 22
2 11 15 12 22 19 56 33 21
3 19 10 21 44 32 44 13 35
4 33 12 34 19 7 16 34 22
....
32...


What I need is a way to compare and assign values to another cell.

if (a1 > a2)
then a34 = a34 + 3
if (b1 > b2)
then b34 = b34 + 3
...
if (a3 > a4)
then a34 = a34 + 3
...
if (f9 > f10)
then f34 = f34 + 3

Also, I'd like to be able to do:
if (a1 is the highest of a1:h1)
then a34 = a34 + 10
if (b1 is the highest of a1:h1)
then b34 = b34 + 10

Thanks,

J
 

JamieDuncan

Board Regular
Joined
Aug 23, 2006
Messages
132
i think a macro would be best as this could mean a mighty big formula,
it is possible to have a macro that will update your cells when changes are made to the sheet
 

Watch MrExcel Video

Forum statistics

Threads
1,108,969
Messages
5,525,960
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top