# if statements ??

#### pamela

##### New Member
I am trying to manipulate an expense statement. I have 4 columns. In column 1 I enter the amt spent for breakfast; column 2 is lunch, column 3 is dinner. In column 4 I want a formula that encompasses several if statements "if column 1 is greater than zero, add \$6" "if column 2 is greater than zero, add \$10" "if column 3 is greater than zero add \$18" the \$6, \$10, and \$18 are per diems allowed by our Client. I then want the formula to total what the per diem should be. for example: let's say column 1 is blank and column 2 and 3 have data ... the result in column 4 should be \$28

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
On 2002-09-10 19:11, pamela wrote:
I am trying to manipulate an expense statement

I trust that's not manipulate in the WorldCom sense of the word!

One method:
Book8
ABCD
1BreakfastLunchDinnerResult
25101534
351524
40101528
5101528
60
7
Sheet1

Post back if you need more,

This message was edited by PaddyD on 2002-09-10 19:36

Hi Pamela,

You have two choices on this
1 is to enter in your total field this formula
=sum(if(isblank(a2),0,6),if(isblank(b2),0,10),if(isblank(c2),0,18))
This will do what you need to do.
Or
2 Choice would allow you to change your per diem can be with out having to change formulas every time.
You can add a sheet for tables and here you would place three cells say A1,B1,C1 and in them put your Per Diem Breakfast Lunch dinner.
Below each put in the 6 10 and 18. Then in cells right below each a3,b3 and c3 place if statements for each testing for blank. Like this one for cell a3

=if(isblank(yoursheet!%A%2),0,a2)
We refer to a2 in this to point to the 6 \$ value. You would do this for each time of day.
Then last on sheet you want total you would put in
=sum(ournewsheet!\$a\$3:\$c\$3)
This will total them for you and if in future you need to increase or decrease the amount per Diem. All you have to do is change 3 cells instead of X number of formulas.

I hope this helps out.
Eric

Wow that is amazing how you did that. Is that using the HTML code?

Eric

Excellent! Exactly what I was trying to do.

One method:
Book8
ABCD
1BreakfastLunchDinnerResult
25101534
351524
40101528
5101528
60
7
Sheet1

Post back if you need more,

This message was edited by PaddyD on 2002-09-10 19:36
[/quote]

On 2002-09-10 19:53, EricL wrote:

Wow that is amazing how you did that. Is that using the HTML code?

Eric

Eric,

A few board members (Colo & Ivan mainly with various others helping out with the testing) have written an addin that converts sheets to html. The addin is available by following the link below this post.

Replies
5
Views
53
Replies
1
Views
92
Replies
1
Views
182
Replies
19
Views
169
Replies
4
Views
170

1,203,485
Messages
6,055,691
Members
444,807
Latest member
RustyExcel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back