Asking the excel formula

Question

for example, when I put 0700 in one column and the last number is "00", the second column want to be 1530, if the first column is 0800, the second column would be 1630, but if the first column I put the number is 0730, means the last 2 number is "30", I want the second column is showed 1600, if 0830 in the first column, the second would be 1700.

How can I set this Formula?

 

One more question, if I put some chinese in the column, the second column will be showed a standard number like 30, but if i the first column is put "off" or "off XXX", means when there is "off" this text is shown in the first column, there is nothing in the second column, is it worked to make a formula?

Thank you so much! Hope you can help.!

Answer 1

Cody,

An alternative way to solve this is to have the 0700, 0730, 0800, 0830 values in the cells A1-A4. The first cell of the second column (B1) should have the formula:

=IF(ISNUMBER(SEARCH(3,A1)),VALUE(A1)+870,VALUE(A2)+830)

The IF statement checks each cell if any of them contains the number "3". If it does then get cell's value and add 870 to it. If it doesn't then adds 830.

Then you copy the formula in B1 to the other cells (B2-B4) to apply the same formula.

Hope this helps.

Answer 2

Hi Cody, a way to do this is the have the 0700, 0730, 0800, 0830 values in the first column as cells A1-A4. The second cell (B1) should have the following formula: =IF(RIGHT(TRIM(A1),2)="00",A1+830,A1+870).

This IF statement states to look at cell A1, which is 0700, and if the last 2 digits have "00" at the end, get cell A1's value and add 830 to it. If it does not end in "00", get cell A1's value and add 870 to it.

Drag B1 formula down the column to apply to the next rows. Let me know if you have any more questions.

-TutorB