excel formulas

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How would I do the following:
A+B = C,
if C = 10 then D = 1
if C = 11 then D = 2
C = 12 then D = 3
and so on
C = 19 then D = 1
thank you!
_________________________________________________________________
 
One way just assuming since you done't say what should happen if C is less
than 10 or greater than 19

=IF(AND(C1<10,C1>19),"none",IF(C1=19,1,C1-9))

Regards,

Peo Sjoblom
 
Tomas,

If A is cell A1, and B is in cell B1, then in cell D1, use the formula

=IF((A1+B1)>9,MOD(A1+B1,9)+ IF(MOD(A1+B1,9)=0,10,0),"")

Not sure if your pattern is meant to continue on indefinitely....

HTH,
Bernie
MS Excel MVP
 
This gives the data you specify, but your example is ambiguous, so I'm
not sure it's what you want:


D: =MOD(C,9)
 
If 19 is to be 1, then here's my attempt. I have A1 holding your sum of
A+B.

=IF(AND(10<=A1,A1<=19),MOD(A1-1,9)+1,"Not 10-19")
 
Peo,
I'd like to ask you for some further help with ceating a formula for:
A1 + A2 = B2
IF A1 + A2 = 2 THEN B2 = 2
IF A1 + A2 = 3 THEN B2 = 3
and so on
IF A1 + A2 = 10 THEN B2 = 1
IF A1 + A2 = 11 THEN B2 = 2
IF A1 + A2 = 12 THEN B2 = 3
IF A1 + A2 = 13 THEN B2 = 4
IF A1 + A2 = 14 THEN B2 = 5
IF A1 + A2 = 15 THEN B2 = 6
IF A1 + A2 = 16 THEN B2 = 7
IF A1 + A2 = 17 THEN B2 = 8
IF A1 + A2 = 18 THEN B2 = 9
IF A1 + A2 = 19 THEN B2 = 1
A1 and A2 must have a value between 1 and 9 otherwise ERROR!

I played with your suggestion but did not get any desired results. I need to
mention that I am new to Excel, so please if you can make it undaerstandable
for dummies.
Thanks a lot!
Tomas
 
One way:

B2: =MOD(A1+A2-1,9)+1

I don't understand what you mean by "A1 and A2 must have a value between
1 and 9 otherwise ERROR!" - how do you get your example's

A1 + A2 = 19 ?????
 
Bernie,
thanks a lot, this was helpful but I noticed I need to ask the right
question in order to get the right answer. So here it is what I want to get:
A1 + A2 = B2
IF A1 + A2 = 2 THEN B2 = 2
IF A1 + A2 = 3 THEN B2 = 3
and so on
IF A1 + A2 = 9 THEN B2 = 9
IF A1 + A2 = 10 THEN B2 = 1
IF A1 + A2 = 11 THEN B2 = 2
IF A1 + A2 = 12 THEN B2 = 3
IF A1 + A2 = 13 THEN B2 = 4
IF A1 + A2 = 14 THEN B2 = 5
IF A1 + A2 = 15 THEN B2 = 6
IF A1 + A2 = 16 THEN B2 = 7
IF A1 + A2 = 17 THEN B2 = 8
IF A1 + A2 = 18 THEN B2 = 9
IF A1 + A2 = 19 THEN B2 = 1
( A1 and A2 must have a value between 1 and 9 otherwise ERROR!)

Thank you VERY much!!!
Tomas
 
Tomas,

I will try to keep the formula relatively simple: a first IF that checks the
input ranges, and the second IF returns values. Still, there isn't any way
to handle the sum of 19 (your last case), since two numbers that are 9 or
less _cannot_ add up to 19.

=IF(OR(A1<1,A1>9,A2<1,A2>9),"ERROR!",IF(A1+A2<10,A1+A2,A1+A2-9))

HTH,
Bernie
MS Excel MVP
 
Thank you Bernie!!! One more question:
Is it possible to add to your statement another function that simply says is
the sum of A1 and A2 is 19, then B2 is 1?
Thanks again,
Tomas
 
Tomas,

=IF(A1+A2=19,1,IF(OR(A1<1,A1>9,A2<1,A2>9),"ERROR!",IF(A1+A2<10,A1+A2,A1+A2-9
)))

HTH,
Bernie
MS Excel MVP
 
IF A1 + A2 = 19 THEN B2 = 1
( A1 and A2 must have a value between 1 and 9 otherwise ERROR!)
Click to expand...

Just an observation. If the max value of A1 or A2 is 9, then the sum will
never equal 19 (max would be 18). Is your question set correctly?

Seems like a form of Mod would work:
=MOD(A1+B1-1,9)+1
 
That was the answer I gave the OP last Friday, but it didn't seem to do
the trick. Given the mathematical impossibility in the problem
statement, I'm darned if I can figure out what is wanted, however...
 
Thank you, Bernie, Dana,

this gave me what I needed. Would you please help me with the next step:

In a column filled with single digits, I am looking for any 2 consecutive
cells, one cell contains "value" a and the next cell contains "value" a+1,
these two cells should become red colored cells. FOR EXAMPLE:
A B
1 5
2 7
3 8
4 2
5 4
6 5
7 9
8 6
..
..
..
100
The cells A2 and A3 and the cells A5 and A6 should be colored RED.

Thanks so much for your help!
Tomas
 
Tomas,

Let's say that your list has a header in cell A1, and the values start in
A2. Select A2 through the end of your list, then use Format, Conditional
Formatting... Select the "Formula is" option, and use the formula

=OR(A2=A1+1,A2=A3-1)

Choose to color the cell red (click the "Format" Button, and click on the
patterns tab) and then click OK to get all the way out, and you're done.

HTH,
Bernie
MS Excel MVP
 
Bernie, THANKS!
I'm not quite there yet, let me try again:

In a column, lets say A1 to A100 filled with single digits, whenever there
is a one digit increase from one row to the next, I want thes two cells
marked "red". e.g:
A
1 6
2 8
3 7
4 7
5 8
6 5
7 1
8 2
9 8
10 9
..
..
100
In this example, I want cell A4 and A5 marked red and A9 and A10 marked red.

Thanks Bernie,
Tomas
 
Tomas said:
In a column, lets say A1 to A100 filled with single digits, whenever there
is a one digit increase from one row to the next, I want thes two cells
marked "red". e.g:
A
1 6
2 8
3 7
4 7
5 8
6 5
7 1
8 2
9 8
10 9 ....
In this example, I want cell A4 and A5 marked red and A9 and A10 marked
Click to expand...
red.

The condition for cell A1 would need to be different than the condition for
the other cells. For A1,

=A2=A1+1

For the other cells, use Bernie's formula.
 
يرجى التكرم بالمواÙقه علي ان اكون معكم
 
Had a hard time figuring out the following "If Then" fomula - so here are the
requirements and the solution to save some people some time

risk = version delta + microcode delta + (1 if monitoring = No) + (1 if %
throughput > 80%) + (1 if % throughput > 90%) + (1 if % throughput > 95%) +
(1 if recovery time > 5 minutes) + (1 if recovery time > 10 minutes) + (1 if
recovery time > 15 minutes) + (2 if recovery time > 20 minutes)


=M4+P4+(IF(Q4="No",1,0))+(IF(U4>0.95,3,IF(U4>0.9,2,IF(U4>0.8,1,0))))+(IF(W4>20,4,IF(W4>15,3,IF(W4>10,2,IF(W4>5,1,0)))))
 
Back
Top