IF Question or other ?

Ghost666th

New Member
Joined
Mar 23, 2009
Messages
20
Column "A" = Can give a figure from 0 to 99
Column "B" = Will determin if "A" returns IF values for > than 39,31,23,15,7.
Coumn "C" = I would like it to place all figures above Column "B" Answer.

ie.

J.Bloggs "A" 56 "B" 40 "C" 16

How do i get "C" ?

New to Excel, did my course Friday (Advanced) but it only goes so far :rolleyes:
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Hi & Welcome to the forum,

It seems with your example

J.Bloggs "A" 56 "B" 40 "C" 16

it is as easy as =A1-B1 to get 16, but I'm sure that is not the case. Your description of Column B is a little confusing

Column "B" = Will determin if "A" returns IF values for > than 39,31,23,15,7

Is it possible to provide more examples with expected results?

http://www.excel-jeanie-html.de/index.php?f=1
 
Upvote 0
J.Bloggs "A" 31.5 "B" 24 "C" 7.5

"B" is based upon the working week of 8hr per day, so as long a a minum of 8hr per day is done it will return BASIC in incriments of 8 ie.

Monday - 8
Tuesday - 16
Wednesday - 24 etc etc

I ask that "C" gives the excess hours worked, ie

Monday - 10hr - "B" will say 8 , im asking that "C" says 2
Tuesday - 10 - "B" will say 16, im asking that "C" says 4

???

Thanks

And thank you for the welcome.
 
Upvote 0
My guess is there are a million and 1 ways to do this, but how about this. There may need to be some other error checking involved. What if A is less than B or would that not be possible?

Sheet3

<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: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 76px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">Mon</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">Tue</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">Wed</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">32</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">Thu</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">40</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">Fri</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>B1</TD><TD>=IF(ISNUMBER(A1),8,"")</TD></TR><TR><TD>C1</TD><TD>=A1-B1</TD></TR><TR><TD>B2</TD><TD>=IF(ISNUMBER(A2),B1+8)</TD></TR><TR><TD>C2</TD><TD>=(A2-8)+C1</TD></TR><TR><TD>B3</TD><TD>=IF(ISNUMBER(A3),B2+8,"")</TD></TR><TR><TD>C3</TD><TD>=(A3-8)+C2</TD></TR><TR><TD>B4</TD><TD>=IF(ISNUMBER(A4),B3+8,"")</TD></TR><TR><TD>C4</TD><TD>=(A4-8)+C3</TD></TR><TR><TD>B5</TD><TD>=IF(ISNUMBER(A5),B4+8,"")</TD></TR><TR><TD>C5</TD><TD>=(A5-8)+C4</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
 
Upvote 0
A will always be greater than B

However your table has answerd a question i made difficult with a simple formula, spent to much time today putting what i recently learnt into practice.

Wasted ur time when a little thought could have resolved.

Sorry & THANK YOU.
 
Upvote 0
Hi and welcome to the board!!
Code:
=IF(A2>39,B2-40,IF(A2>31,B2-32,IF(A2>23,B2-24,IF(A2>15,AB-16,B2-8))))
lenze
 
Upvote 0
Does your worksheet always stay within a single day? I have one that was given from this board that will still do a good calculation even if your workbook/timecard goes from one day into the next, like for a shift worker who starts at 18:00 pm and works till 02:00 am the next day...

Excel Workbook
KLMNOP
19
20Start timeend timehours workedovertime
2118:0002:008:000:00
2218:0003:009:001:00
2318:0002:458:450:45
2417:4504:0010:152:15
2510:0018:458:450:45
26
Sheet1


In this example O20 is actually showing overtime but in reality the cell contains 08:00 hours, i've just formatted it to show "overtime"

Hope this helps also
 
Upvote 0
Excel Workbook
MNOP
27
28BASICovertime
298:00 
309:001:00
318:450:45
3210:152:15
338:450:45
3408:00 
3508:00 
3608:150:15
37 
38 
39
Sheet1



and this is another version using the same formatting if all you wish for is a total hours input box

I'm just giving additional options, i appreciate you've already found a solution
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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