Free Microsoft Excel 2013 Quick Reference

IF function for timesheet

So I am going to try and explain this... Not sure if it will make any sense. I know it will probably be a little wordy and who know if anyone will even understand.

I am making a excel timesheet. I have several columns and what I need to do and I don't know if and "IF" function is possible or if there is some other way. Each day is its own row so there are 7 rows on the chat (not including the header row) so they have a date column,start time(this is a drop down box), end time (Drop down), then a sub total hours (which is a cumualtive total for the week(ie: row one if worked 5 hours for the day will say 5 , then if on day 2 they work 5 hours it will show 10)) here is where my problem comes in. My guys can work overtime this doesn't start until the hours hit 40 in the week (not if working more then 8 hours a day). What I want to do is to have two columns one for reg hours and one of OT hours and have this information feed off the sub total hours column. I don't know if I can do this with an IF function or if there is maybe a better way. I have tried but can't seem to get it to work. If you have any insight on what I should do that would be great, if not thanks for looking... If you have any questions please be sure to ask and I will answer what I can...

Kat


Hey guys,

I would like to create an IF FUNCTION for a Combo Box item (which will be executed through a macro in the combo box). The function will look at the item selected in the combox box and perform a certain action if the item selected is of a certain value.

For example:
If a combo box has a list of numbers: 0, 1, 2, 3, 4, 5
The numbers correspond to a set of cells below (lets say row 2)
When numbers 1, 2, 3, 4, or 5 are selected from the box row 2 unhides.
But when number 0 (null) is selected from the box, row 2 hides.

Anyway to get an if function implemented for a combox box item? I know I can simply have it look at the combox boxes linked cell (which it does have), but I first just want to see if I can have it look right at the box itself.

Any help is appreciated.

How can I use IF function for two conditions in a cell like below:

Cell A1 contains data ABC
Cell A2 contains data 500

I would like to set a IF function in cell A3 that IF A1=ABC and A2=500,
then A3 changes to "50KGS"
--
wuwu

HI all, total newbie to this whole excel thing.

Is there a way to use an IF function for several cells with a hyperlink function tied in e.g

=IF(A1=100,Hyperlink("www.exceltip.com","false"))

that seems to work, but i want it to apply if any cell in the A range =100, how do i do it?

ALSO

is it possible to link another If function to the A range e.g

=IF(A1=200,HYPERLINK("www.excelforum.com","false"))

Can this be made to add things into a cell, if for example i wanted to make this function to add the word "car" into a different cell than the one the function is in.

Seems like i have asked a lot. Sorry guys

hi, im trying to produce an if function to sort out the time in which free kicks were taken in the premier league to determine whether more free kicks happen in the latter stages than the start.

also i want to produce a bar chart to show the times when free kicks happened in a soccer match. the free kicks happen at different times during a game so i want to separate them into the following groups. i already have the free kick times listed, over 800 free kicks
can someone help me create an if function for this??

1st half
0-15mins
15-30mins
30-50 mins (50 because of added on time)

2nd half
45-60
60-75
75-95

i want to produce a bar chart to show the times when free kicks happened in a soccer match. the free kicks happen at different times during a game so i want to separate them into the following groups. i already have the free kick times listed, over 800 free kicks
can someone help me create an if function for this??

1st half
0-15mins
15-30mins
30-50 mins (50 because of added on time)

2nd half
45-60
60-75
75-95

Hello,

I want to do a count if function for all 12 months, by month, between the four weeks in that month as part of a monthly roll up so I can roll up by year also. The program will not let me =SUM(all 52 weeks data). Anyone know how to do this because I am stumped!

hi all

for the situation whereby i need to link up data over a range of 2 sheets, i tried using IF function, looks pretty complicated, attached is the results, still couldnt get it 2 work, but i think i am on the rite track

any suggestions for my formula?
thanks..

stan

hi, I've been playing with an if function i just cant figure out for about close to an hour, i figure it will be much faster just asking someone experienced.

I have three columns. A, B, C lets say

Each have many rows of data in them

the function im trying to do is, "if the Sum of Column A is between 25 and 35% of the sum of columns A, B and C make the cell Green, if not, make it red?

another easier question that i need is "If sum of column D is between 470 and 530 make cell green color, if not, make red"

if the color thing is too hard to do, ill just make it say true false or whatever, just would be more aesthically pleasing if it was red or green. The main thing is the formula

thanks in advance

Can someone let me know what the VBA code is for an "IF" function when an error comes up. In one of my macros an error occurs in some instances. What I want is some code that states:

If the error "Out of memory" is activated, then xxx, otherwise continue

Thanks

Tom

Hi all, I have a question regarding VBA code for an if function.

In looking for an answer, I found this code provided on a website
(www.techonthenet.com) It seems to be what I'm looking for but returns
the error '#NAME' when I try and add it to Excel. Does anyone know
what's wrong with the code? The strange thing is that there's an
example of this as a download which seems to return the same error, I'm
wondering if it's something to do with the version of Excel I'm
using... (I have 2003)

Function CalcValue(pVal As String) As Long

If pVal = "10x12" Then
CalcValue = 140

ElseIf pVal = "8x8" Then
CalcValue = 64

ElseIf pVal = "6x6" Then
CalcValue = 36

ElseIf pVal = "8x10" Then
CalcValue = 80

ElseIf pVal = "14x16" Then
CalcValue = 224

ElseIf pVal = "9x9" Then
CalcValue = 81

ElseIf pVal = "4x3" Then
CalcValue = 12

Else
CalcValue = 0
End If

End Function

The values in the code are not the one's I would be using, but they are
the ones provided in the example (which also does not seem to work).

In Sheets 1-8 I have calendars for different people w/ 8 on days they
have off

In Sheet9 I have in Column A days 1-31
In column B I have: =IF(Carlos!B3=8,"Carlos")

My problem/question is can I use the IF function to get multiple
answers? If Carlos and Jim both have the same day off, what function
would I use?

Thanks,

--
fastballfreddy
------------------------------------------------------------------------
fastballfreddy's Profile: http://www.excelforum.com/member.php...o&userid=33986
View this thread: http://www.excelforum.com/showthread...hreadid=537551

Good day,

Kindly pls help to build the right IF function formula for production schedule. The
details as per below.

Total amount (qty)=30,000
Daily output= 1,500
2nd process required qty/day=1,200
2nd process date (reference)=8 June 05
1st process date (start)= should DEDUCT 5 days from 2nd process date (3 june 05)

So, as refer to above condition, pls help me to calculate 1st process output from 3 june onwards by using right "IF" function?

I would like to know hown many days the process take?

GREAT APPRECIATION ON YOUR HELP..........

From,
Syima

Hi i need help for my phd work, I have a data set and there is a questionnaire that each questions in it has more than one answer. I can give an example like below.

For example for question 1 in the questionnaire if students said 1 i have to give 0 point, for the same question if student said 2 i have to give 1,5 point, for 3 i give 2,5 point like this.

How can asses the data like this (you can see the file excerpt in the appendix) with if function.

In Sheets 1-8 I have calendars for different people w/ 8 on days they have off

In Sheet9 I have in Column A days 1-31
In column B I have: =IF(Carlos!B3=8,"Carlos")

My problem/question is can I use the IF function to get multiple answers? If Carlos and Jim both have the same day off, what function would I use?

Thanks,

I work in communications, and want to display a spreadsheet that shows whether a transmitter can be receiveed by particular receivers as follows:

Data Rows:

Transmitter....LOW FREQ...HI FREQ......Receiver......LOW FREQ....HIGH FREQ
XTR 1............123 Mhz......300 Mhz......RCVR1........ 50 Mhz.......1000 Mhz

Since the receiver's capabilities are 50 - 1000 Mhz, the transmitter falls into it's reception range. So I would use a function in a cell next to the Receiver name to result in a simple 1 or 0. 1 being capable or receiving that transmitter, 0 not capable. Then use Conditional Formatting to turn that cell Green or Red respectively based on the 1 or 0.

I have tried nested IF functions to no avail. If any part of the transmitter number range falls within the receiver's number range, I want to simply color a cell green or red.

Sorry for the verbose post, hope I got my point across, and appreciate any assistance.

Thanks in advance for the help.

I'm needing to use the "IF" function in a formula for the first time, at least I think that's my solution.

I've got a spread sheet made of several worksheets. Each worksheet grows with new entries every day. As a new item is added to a row, I need it to total on another page based on a variable in column "C"

1. Each individual worksheet has about 15 columns. Column "C" is the one I am trying to refer to in my formula. Columns "D" through "O" represent the months, Jan - Dec.

2. My final worksheet needs to total numbers from all other sheets according to the value entered in column "C".

For examples sake, lets say my worksheets are called

blue, green, red, yellow, purple and orange, with orange being the sheet I am totaling everything on.

Further, lets say that the values in column "C" are blueberry, lime, apple, banana, and plum.

There are numerous destination cells in the orange sheet where I need something like the following:

"if cell C2 on the blue worksheet = blueberry, add it to the total in this cell, if it equals anything else, ignore it if cell C2 on the green worksheet = blueberry, add it to the total in this cell, otherwise ignore it." this formula needs to continue so it references cell C2 on each sheet. When the formula is completed, I'll drag it across to reference the other columns across the sheet.

in the cell below it, I'll need something such as: "if cell C2 on the blue worksheet = lime, add it to the total in this cell, if it equals anything else, ignore it" and so on as listed above.

Hope I haven't lost everyone. Thanks again for your help.

What site is best for learning to use "IF" functions in EXCEL???

Hi I need to know if anyone can give me an IF Function that will calculate
the number of days remaining in a given month. For example, if the date is
July 17th, the calculation would be 31-17+1=15 days. My problem is setting
the citeria to recognize whether the month is a 31 day month/30 day month or
28/29 day month (I would prefer not having to enter in that information for
each date). Please help! Thank you!

hi, I got this problem when I tried to make a function for reading numbers and translate it into words. like this code below:

=IF(I40>=1000000,IF(ROUNDDOWN(I40/1000000,0)>=100,VLOOKUP(VALUE(LEFT(ROUNDDOWN(I40/1000000,0),1)),read_2!$A$1:$B$99,2,FALSE)&"hundred ","")&IF(VALUE(RIGHT(ROUNDDOWN(I40/1000000,0),2))>=1,VLOOKUP(VALUE(RIGHT(ROUNDDOWN(I40/1000000,0),2)),read_2!$A$1:$B$99,2,FALSE),"")&"million ","")&IF(MOD(I40,1000000)<1000,"",IF(MOD(I40,1000000)>=100000,VLOOKUP(VALUE(LEFT(MOD(I40,1000000),1) ),read_2!$A$1:$B$99,2,FALSE)&"hundred ","")&IF(MOD(I40,100000)>=10000,VLOOKUP(VALUE(LEFT(MOD(I40,100000),2)),read_2!$A$1:$B$99,2,FALSE),IF (MOD(I40,100000)>=1000,VLOOKUP(VALUE(LEFT(MOD(I40,100000),1)),read_2!$A$1:$B$99,2,FALSE),""))&"thous and ")&IF(MOD(I40,1000)>=100,VLOOKUP(VALUE(LEFT(MOD(I40,1000),1)),read_2!$A$1:$B$99,2,FALSE)&"hundre d ","")&IF(MOD(I40,100)>=1,VLOOKUP(VALUE(RIGHT(MOD(I40,100),2)),read_2!$A$1:$B$99,2,FALSE),"")&")"

I tried this code manually (by entering the numbers on my own)and it works! but the problem is when I apply it into a formulated cell (a cell that containing =sum())

for example:

if the cell contain the value of 1,689,750

it would be read into:

"one million six hundred eighty nine thousand seven hundred seventy one"

it's not read as "fifty".

and, I made a sheet (data table) for "translate" numbers into words in a range of 1 - 99. and is called "read_2"

this is so confusing because i didn't find an error for my code because when I tried to enter it manually, it shows a normal result.

I'm waiting for your replies

thanks

Hello,

I am trying to build a spreadsheet that calculates a GPA.

Basically what I have is the letter grades in the C column and the value of the letter grades in D column. Instead of entering the letter grade and then the value of the letter grade, I want excel to know what the value of each letter grade is and display it accordingly in the next column.

I can figure out how to make it work with one letter:
=IF(($C2="A"),"4.0","")

I want to add in

=IF((C2="A-"),"3.7","")

along with

=IF((C2="B+"),"3.3","")

etc.

Is the IF function the wrong function to do this with? How could I go about doing something like this?

Thanks a lot for your help
Sean

hi, I've been playing with an if function i just cant figure out for about close to an hour, i figure it will be much faster just asking someone experienced.

I have three columns. A, B, C lets say

Each have many rows of data in them

the function im trying to do is, "if the Sum of Column A is between 25 and 35% of the sum of columns A, B and C make the cell Green, if not, make it red?

another easier question that i need is "If sum of column D is between 470 and 530 make cell green color, if not, make red"

if the color thing is too hard to do, ill just make it say true false or whatever, just would be more aesthically pleasing if it was red or green. The main thing is the formula

thanks in advance

Hello everyone

I'm studying an Excel course and I need to use the IF function for some employment table, like this:

If cell E2(employment status) equals I34(salaried employee) OR I35(self-employed), then value_if_true=J36(employed), value_if_false=J38(unemployed). I just don't know what is the right character for "OR". I know that : is for through (cell x through cell y), + is for and (cell x plus cell y), and so on, but I couldn't for the life of me find what is the right one for "OR". Does anybody know?

I'm using Office 2010, if it means anything.

Any help will be greatly appreciated.

Hi, I am stumped on how to do this problem. I have done really basic IF functions and Vlookup for simple grading but I have no idea how to tackle this problem.

Table:
Premier tickets: $65 ( 22and under), $90 (Over 22)
Floor Tickets: $50 (22 and under), $70 (Over 22)
Balcony tickets: $30 (22 and under), $30 (Over 22)
Standing Room: $15 (22 and under), $20 (Over 22)

Question states:
Create a spreadsheet that allows a person to enter in an age and a ticket location (Premier tickets, floor tickets, Balcony tickets, Standing Room) and automatically give the price of the ticket. Use IF and Vlookup functions and use appropriate addressing for your functions.

Here's a picture of the chart:
http://img299.imageshack.us/i/excelchart.jpg/

Hi all, I have a question regarding VBA code for an if function.

In looking for an answer, I found this code provided on a website
(www.techonthenet.com) It seems to be what I'm looking for but returns
the error '#NAME' when I try and add it to Excel. Does anyone know
what's wrong with the code? The strange thing is that there's an
example of this as a download which seems to return the same error, I'm
wondering if it's something to do with the version of Excel I'm
using... (I have 2003)

Function CalcValue(pVal As String) As Long

If pVal = "10x12" Then
CalcValue = 140

ElseIf pVal = "8x8" Then
CalcValue = 64

ElseIf pVal = "6x6" Then
CalcValue = 36

ElseIf pVal = "8x10" Then
CalcValue = 80

ElseIf pVal = "14x16" Then
CalcValue = 224

ElseIf pVal = "9x9" Then
CalcValue = 81

ElseIf pVal = "4x3" Then
CalcValue = 12

Else
CalcValue = 0
End If

End Function

The values in the code are not the one's I would be using, but they are
the ones provided in the example (which also does not seem to work).