| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
excel formulas
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! __________________________________________________ _______________ |
|
#2
|
|||
|
|||
|
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,C119),"none",IF(C1=19,1,C1-9)) Regards, Peo Sjoblom "Tomas" wrote: 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! __________________________________________________ _______________ |
|
#3
|
|||
|
|||
|
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 "Tomas" wrote in message ... 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! __________________________________________________ _______________ |
|
#4
|
|||
|
|||
|
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) In article , "Tomas" wrote: 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 |
|
#5
|
|||
|
|||
|
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") -- Dana DeLouis Win XP & Office 2003 "Tomas" wrote in message ... 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! __________________________________________________ _______________ |
|
#6
|
|||
|
|||
|
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 "Peo Sjoblom" wrote: 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,C119),"none",IF(C1=19,1,C1-9)) Regards, Peo Sjoblom |
|
#7
|
|||
|
|||
|
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 ????? In article , "Tomas" wrote: 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. |
|
#8
|
|||
|
|||
|
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 "Bernie Deitrick" wrote: 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 "Tomas" wrote in message ... 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! __________________________________________________ _______________ |
|
#9
|
|||
|
|||
|
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,A19,A2<1,A29),"ERROR!",IF(A1+A2<10,A 1+A2,A1+A2-9)) HTH, Bernie MS Excel MVP "Tomas" wrote in message ... 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 "Bernie Deitrick" wrote: 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 "Tomas" wrote in message ... 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! __________________________________________________ _______________ |
|
#10
|
|||
|
|||
|
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 "Bernie Deitrick" wrote: 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,A19,A2<1,A29),"ERROR!",IF(A1+A2<10,A 1+A2,A1+A2-9)) HTH, Bernie MS Excel MVP "Tomas" wrote in message ... 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 "Bernie Deitrick" wrote: 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 "Tomas" wrote in message ... 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! __________________________________________________ _______________ |
|
#11
|
|||
|
|||
|
Tomas,
=IF(A1+A2=19,1,IF(OR(A1<1,A19,A2<1,A29),"ERROR!" ,IF(A1+A2<10,A1+A2,A1+A2-9 ))) HTH, Bernie MS Excel MVP "Tomas" wrote in message ... 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 "Bernie Deitrick" wrote: 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,A19,A2<1,A29),"ERROR!",IF(A1+A2<10,A 1+A2,A1+A2-9)) HTH, Bernie MS Excel MVP "Tomas" wrote in message ... 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 "Bernie Deitrick" wrote: 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 "Tomas" wrote in message ... 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! __________________________________________________ _______________ |
|
#12
|
|||
|
|||
|
IF A1 + A2 = 19 THEN B2 = 1
( A1 and A2 must have a value between 1 and 9 otherwise ERROR!) 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 -- Dana DeLouis Win XP & Office 2003 "Tomas" wrote in message ... 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 "Bernie Deitrick" wrote: 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 "Tomas" wrote in message ... 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! __________________________________________________ _______________ |
|
#13
|
|||
|
|||
|
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... In article , "Dana DeLouis" wrote: Seems like a form of Mod would work: =MOD(A1+B1-1,9)+1 |
|
#14
|
|||
|
|||
|
Ours is not to reason why....
Bernie I'm darned if I can figure out what is wanted, however... |
|
#15
|
|||
|
|||
|
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 |
|
#16
|
|||
|
|||
|
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 "Tomas" wrote in message ... 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 |
|
#17
|
|||
|
|||
|
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 "Bernie Deitrick" wrote: 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 "Tomas" wrote in message ... 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 |
|
#18
|
|||
|
|||
|
"Tomas" wrote...
.... 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 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. |
|
#19
|
|||
|
|||
|
يرجى التكرم بالموافقه علي ان اكون معكم
|
|
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF THEN 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(U40.95,3,IF(U40.9,2 ,IF(U40.8,1,0))))+(IF(W420,4,IF(W415,3,IF(W410 ,2,IF(W45,1,0))))) "Tomas" wrote: 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! __________________________________________________ _______________ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
| How to make Excel run limited number of formulas on a given worksh | Excel Discussion (Misc queries) | |||
| Way to make Excel only run certain formulas on a worksheet? | Excel Discussion (Misc queries) | |||
| How do I get to master Excel functions and formulas? | Excel Worksheet Functions | |||
| Problems with Excel formulas when 2002 upgraded to XP | Excel Worksheet Functions | |||
Linear Mode
