Help with Excel based timesheet - Page 2 - Perth-WRX.com

westoz · #11 27-06-2012, 08:22 PM

If it helps, this is the data I'm working with to achieve the value at I23:

Days worked / Total Pay / Tax Withheld

6 / 904.25 / 154
5 / 753.54 / 103
4 / 602.83 / 69
3 / 452.12 / 44
2 / 301.42 / 14
1 / 150.71 / 0

Sharpy.au · #12 27-06-2012, 08:24 PM

Also just remember there is room for error with this - if any value aside from the exact figures mentions appears the formula will give a $0 withholding value. Consider using data validation to ensure you only get one of the 6 options, even a cent out will give 0

gumby · #13 27-06-2012, 08:24 PM

Quote:

Originally Posted by westoz

here is my (failed) attempt, where i21 is the total pay, and the following formula is input into i23, which is the tax withheld value:

=if(i21=904.25,154,154,=if(i21=753.54,103,103,=if( i21=602.83,69,69,=if(i21=452.12,44,44,=if(i21=301. 42,14,14,=if(i21=150.71,0,0))))))

maybe i need to use or formulae? I'm a bit if a noob at excel

.

=if(i21=904.25,154,if(i21=753.54,103,if(i21=602.83 ,69,if(i21=452.12,44,if(i21=301.42,14,if(i21=150.7 1,0,0))))))

Sharpy.au · #14 27-06-2012, 08:28 PM

Quote:

Originally Posted by Sharpy.au

Try this:

=IF(I21=904.25,154,IF(I21=753.54,103,IF(I21=602.83 ,69,IF(I21=452.12,44,IF(I21=301.42,14,IF(I21=150.7 1,0,0))))))

Just review the numbers to make sure they are right. You were very close, you just replace the false statement with another IF function instead and don't use = other than at the beginning

Replace the last 0 with "##" so you know when it is 0 and when it has the wrong figures

westoz · #15 27-06-2012, 08:31 PM

Quote:

Originally Posted by Sharpy.au

Try this:

=IF(I21=904.25,154,IF(I21=753.54,103,IF(I21=602.83 ,69,IF(I21=452.12,44,IF(I21=301.42,14,IF(I21=150.7 1,0,0))))))

Just review the numbers to make sure they are right. You were very close, you just replace the false statement with another IF function instead and don't use = other than at the beginning

OK, tried that... I don't get an error dialogue this time, but the value at I23 doesn't calculate, instead I get a single minus sign (-).

Sharpy.au · #16 27-06-2012, 08:36 PM

Is the value in i21 causing it to equal 0?

Replace the final zero in the formula with "##" (including the quotations) and see what happens

westoz · #17 27-06-2012, 08:45 PM

OK, finally got it sorted

This was my final working formula:

=IF(I21=904.248,154,IF(I21=753.54,103,IF(I21=602.8 32,69,IF(I21=452.124,44,IF(I21=301.416,14,IF(I21=1 50.708,0))))))

You will notice that the Total Pay in the formula has been corrected to the 3rd decimal place, despite the fact that the Total Pay at I21 on the timesheet is rounded up or down to the nearest cent to the 2nd decimal place. This is what was causing all the errors.

Thanks heaps for all your help, much appreciated

Sharpy.au · #18 27-06-2012, 08:49 PM

Just so you know for future reference, the increase/decrease decimal places functions are purely a display thing. If you want to round you would need to wrap the I21 formula in a ROUND function. Would help ensure you don't get a value of 0 for withholding when it should be something else

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Report: Peugeot developing compact crossover based on 208 hatch	rss.feed	General Subaru Discussion	0	07-01-2012 12:00 AM
Hyundai Excel 1997 $1500ono	mrclubspecevo4	For Sale (Cars Only)	0	17-12-2010 09:27 PM
Microsoft Excel HELP	Wiiha	Non-WRX Discussion	2	26-01-2008 08:27 AM
MY2000 Excel Headlights	hybridgx	Wanted	0	12-04-2007 08:41 PM
Help with Microsoft Excel	ImPreSiV	Non-WRX Discussion	5	08-03-2007 12:52 PM

The Following User Says Thank You to gumby For This Useful Post:
westoz (27-06-2012)