trying to add a complicated mess to my worksheet... :)

H

hakkabuff

hi, I'm not really familiar with Excel (came with my PC) but I thought it
could help me with some minor issues. What I'm trying to do is to make a cell
display a specific number that can change if a value of another cell is
modified.
for example if a cell contains the number "13" the other one should display
"+1" and if the "13" should change to "18" the "+1" should change to "+4", I
tried using the IF function but I'm not familiar with Basic so I couldn't do
what I wanted. The range of numbers I wanted are [2 & 3 = -4, 4 & 5 = -3, 6 &
7 = -2, 8 & 9 = -1, 10 & 11 = 0, 12 & 13 = +1, 14 & 15 = +2, 16 & 17 = +3, 18
& 19 = +4, 20 & 21 = +5, and so on], any help on this matter would be very
much appriciated.
 
P

Peo Sjoblom

You could create a table that looks like

2 -4
4 -3
6 -2
8 -1
10 0
12 +1
14 +2
16 +3
18 +4
20 +5

call it MyTable (select it band do insert>name>define and type the name or
type it in the namebox)
then use a formula like

=IF(A1<2,"",VLOOKUP(A1,MyTable,2))

or hardcoded


=IF(A1<2,"",VLOOKUP(A1,{2,"-4";4,"-3";6,"-2";8,"-1";10,"0";12,"1";14,"+2";16,"+3";18,"+4";20,"+5"},2))

note tyhat I am using text representations of the numbers -4 - +5
you could use numeric values and use a custom format like

+General;-General;0
 
D

Duke Carey

Assume the cell that contains the number 13 is cell A1, then put this formula
in the cell you want to change:

=-4+INT((A1-2)/2)
 
H

hakkabuff

what I'm really trying to do is, I have 2 cells A3 and C3 that I type a
number into and a third that adds the previous 2 cells together, example; I
type -2 in A3 and 16 in C3 and I have a =SUM(A3;C3) in cell D3 that displays
the final number, wich would in this case be 14, now if you've played D20 rpg
you should see what I'm trying to do, in cell E3 I'm trying to display a
bonus that relates to the sum in cell D3, so if D3 is 14 or 15 then E3 should
display +2 but if it's any other number it should display the bonus relevant
to that number, let's say if it changed from 14 to 37 it should display +13
and if the number is 8 or 9 then it should be -1.... it really is a
complicated mess.... :s
 
Top