if statements ??

pamela

New Member
Joined
Sep 9, 2002
Messages
4
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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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,

Paddy
This message was edited by PaddyD on 2002-09-10 19:36
 
Upvote 0
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
 
Upvote 0
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,

Paddy
This message was edited by PaddyD on 2002-09-10 19:36
[/quote]
 
Upvote 0
On 2002-09-10 19:53, EricL wrote:
PaddyD

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.

Paddy
 
Upvote 0

Forum statistics

Threads
1,223,497
Messages
6,172,644
Members
452,467
Latest member
colelkay

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top