Using IF and OR in the same formula

[melamaric]

Hi: I want to write a formula that asks if TWO conditions are true, i.e., I want to ask:
if there is a value in column E OR there is a value in column I, multiply column Column H and I together.

 

[VoG]

Perhaps


=if(or(e1<>"",i1<>""),h1*i1,"")

 

[melamaric]

Sorry, I didn't forgot about another component.
So, if there is a value in E or a value in I, I want to multiply H & I together AND add value in E as well. So the total will either be just what is entered in column E, or else it will be what is entered in column E PLUS the value of H times I.

 

[im2bz2p345]

By "value," do you mean a number?

I came up with this:

=IF(OR(ISNUMBER(E1),ISNUMBER(I1)),H1*I1,"No Numbers in Column E or Column I")

This assumes that there are valid numbers in Column H that can be multiplied by the number Column I.

Hope that helps,

~ Im2bz2p345

 

[im2bz2p345]

Sorry, I didn't forgot about another component.
So, if there is a value in E or a value in I, I want to multiply H & I together AND add value in E as well. So the total will either be just what is entered in column E, or else it will be what is entered in column E PLUS the value of H times I.

I am not understanding this. Let's say for example there is a number in column I and column H, but there are some letters in column E.

The formula would check if there is a number in either column I OR column E (which in this example, only column I has a number), so it would continue to multiply the number in column H by the number in column I AND add the letters in column E? You can't add numbers & letters, so it would give you an error.

Please clarify what you mean,

~ Im2bz2p345

 

[melamaric]

I didn't mention letters . . . they are all numbers.
Okay, so here's the scenario:

I want to be able to get a rep to input his start and finish time, which will then result in number of hours spent.
Sometimes, however, he will have receipts (in dollar amounts) to submit on the SAME line.
Other times, he will only have receipts to submit on one line.
So, I want to be able to have him input just his receipts and get a total at the end, OR
I want him to be able to input his receipts, plus his hours (resulting in number of hours spent, which will then calculate to dollars after he inputs his hourly rate), which will give him TOTAL amount owing.

On January 1, he spends $5 in supplies and he wants to be reimbursed but has not spent any hours in sales, so his total reimbursement for that date will be $5.

On January 2, he spends $8 in supplies AND he worked from 3:00 p.m. to 11:00 p.m. at a rate of $12/hr. So on that date, he gets $104 reimbursed.

I hope that makes more sense.

 

[im2bz2p345]

The point about letters was that since you have an OR statement, there is no value/number validation testing being done solely on column E (it could have something random in it such as letters in my previous example and then your formula would fail).

Regardless if you're for sure that Columns E, H, and I will all be numbers, here is what you might be after:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 90px"><COL style="WIDTH: 81px"><COL style="WIDTH: 81px"><COL style="WIDTH: 90px"><COL style="WIDTH: 75px"><COL style="WIDTH: 108px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Receipts Total</TD><TD>Started Shift</TD><TD>Ended Shift</TD><TD>Hours Worked</TD><TD>Hourly Rate</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Reimbursement</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">$8 </TD><TD style="TEXT-ALIGN: center">3:00:00 PM</TD><TD style="TEXT-ALIGN: center">11:00:00 PM</TD><TD style="TEXT-ALIGN: center">8:00</TD><TD style="TEXT-ALIGN: center">$12 </TD><TD style="TEXT-ALIGN: center">$104</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">$8 </TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">0:00</TD><TD style="TEXT-ALIGN: center">$12 </TD><TD style="TEXT-ALIGN: center">$8</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>H2</TD><TD>=G2-F2</TD></TR><TR><TD>J2</TD><TD>=IF(OR(ISNUMBER(E2),ISNUMBER(I2)),((H2*24)*I2)+E2,E2)</TD></TR><TR><TD>H3</TD><TD>=G3-F3</TD></TR><TR><TD>J3</TD><TD>=IF(OR(ISNUMBER(E3),ISNUMBER(I3)),((H3*24)*I3)+E3,E3)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

The reason column H is being multipied by 24 is to convert the time (which Excel treats as a fraction of an entire day - i.e. 8/24) into a whole number.

Hope that helps,

~ Im2bz2p345

 

[melamaric]

I actually had the number of hours spent already multiplied by 24.
Also, the end value cannot just be whatever is contained in column E because there may be times when hours worked are entered but no receipts, so I amended the formula as follows and it works beautifully:

=IF(OR(ISNUMBER(E2),ISNUMBER(I2)),((H2*I2))+E2,"")


Thank you very much for your help. Much appreciated!!

 

[im2bz2p345]

I actually had the number of hours spent already multiplied by 24.
Also, the end value cannot just be whatever is contained in column E because there may be times when hours worked are entered but no receipts, so I amended the formula as follows and it works beautifully:

=IF(OR(ISNUMBER(E2),ISNUMBER(I2)),((H2*I2))+E2,"")

Thank you very much for your help. Much appreciated!!

Glad you worked it out to your liking. I actually had my formula similar to what you posted above as your final solution, but you had mentioned this earlier:

Sorry, I didn't forgot about another component.
So, if there is a value in E or a value in I, I want to multiply H & I together AND add value in E as well. So the total will either be just what is entered in column E, or else it will be what is entered in column E PLUS the value of H times I.

You state here that the total reimbursement will be either Column E OR Column E + H*I.

Hope that explains my reasoning,

~ Im2bz2p345