View Full Version : Sleeper: Payroll File--Need Help with Formula
rsrasc
04-28-2023, 02:15 AM
Hi all,
I need some help with the formulas from Column Q thru Column AB based on the information that are currently in the file from rows 9 thru 25.
The formulas are based on the value from Column M.
For example, if a cell in Colum M has a value, for example of 7, that line will give me the values for the last 7 months of the year, which will be from Mar thru Sep.
If the value is 12, then it will give me the cost for 12 months (Oct thru Sep).
What I need is the following. I would like to do the opposite when a number is negative.
If I put for example a minus number in Column M, for example, minus -5, I would like to get the value for the first months of the year, which will be from Oct thru Feb, and from Mar thru Sep the values will be blank.
I have tried so many options with the existing formula in th cells but I can't get it to work.
I will appreciate if someone can assist me.
Thank you in advance!
Regards/Cheers.
georgiboy
04-28-2023, 02:33 AM
Here is an example of that being done:
=IF($M14>11,(ROUND((($N14*(8*($O14/40)))*Q$5)*(1+Q$6),0)),IF($M$14<=-1,(ROUND((($N14*(8*($O14/40)))*Q$5)*(1+Q$6),0)),0))
Then it would be <=-2 for the next column etc...
rsrasc
04-28-2023, 03:19 AM
Hi georgiboy,
Wow! I'm impress. Working great!
Much appreciated.
Regards!
rsrasc
04-28-2023, 04:23 AM
Hi Georgiboy,
I'm hoping you can assist me with one (maybe two) more request. If I want to apply the same concept for having a minus in Column M, and for example, what will be the formula for row Q47, and Q83 based on the existing formula there.
Thanks!
georgiboy
04-28-2023, 04:50 AM
Q47:
=IF($M47>11,($N47*Q$5),IF(M$47<=-1,($N47*Q$5),0))
Same logic for Q83
rsrasc
04-28-2023, 05:39 AM
Hi georgiboy, thank you again for assisting me. I tested and is working great! Much appreciate it.
rsrasc
04-28-2023, 10:31 AM
Hi Georgiboy, I think I need an extra little bit of help. I'm using the below formula for cell Q748 but it is given me the following erro: "#Value!" Would you please also help me with this one? Thanks!
=IF($M748>11,IF(Q712<130000,((Q677-Q560)*$N748),0)),IF($M748<=0,IF(Q712<130000,((Q677-Q560)*$N748),0))
Aussiebear
04-28-2023, 05:46 PM
Do you have a Value in $M748?
rsrasc
04-29-2023, 12:57 AM
The value for $M748 should be 12. When I submitted the attachment, I didn't have any values in Column M.
rsrasc
04-29-2023, 01:06 AM
The value for $M748 should be 12. When I submitted the attachment, I didn't have any values in Column M. <br><br><br>
Sorry, but the value for $M748 should be -7. Since georgiboy provided some help with the formulas, I decided to apply his formulas to the rest of the document. So, now I'm adding info to Column M.
Aussiebear
04-29-2023, 01:12 AM
I suspect the formula failed before because you didn't have a value in M748
rsrasc
04-29-2023, 02:01 AM
30771
=IFERROR(ROUND(IF(Q712<130000,((Q677-Q560)*$N748),0),$E$1),"-")
Hi all, the attached formula in working from cell Q747 to AB759 (with values in Column M).
but this one doesn't work.
=IF($M748>11,IF(Q712<130000,((Q677-Q560)*$N748),0)),IF($M748<=0,IF(Q712<130000,((Q677-Q560)*$N748),0))
Aussiebear
04-29-2023, 04:51 AM
Rsrasc. Be serious here. That image you presented could be from anywhere. Post your Workbook so we can examine the how, where, and why.
rsrasc
04-29-2023, 05:31 AM
Rsrasc. Be serious here. That image you presented could be from anywhere. Post your Workbook so we can examine the how, where, and why.
Hi Aussiebear,
Not a problem. My apology.
See attached file.
BTW, something else. I have the following formula in Cell Q860, which is working. Is showing $118.41.
With you help and an updated formula can this number be rounded to $118.00 instead of $118.41?.
[CODE]IF(P860<>"R","-",IF($M860>11,(Q677*$N860),IF($M860<=0,(Q677*$N860),0)))
Thank you for your assistance.
Aussiebear
04-29-2023, 06:49 AM
Man, you are like chasing a rabbit down a hole. You ask a question but that tis not what you want. Cell M748, remember that.... Once you filled in a value, did the formula work?
With regards to your new request ( which by the way should have been another new thread). Review the Rounddown function by Microsoft.com
rsrasc
04-29-2023, 07:50 AM
In regards to cell M748, remember that.... Once you filled in a value, did the formula work? To answer you question, the answer is yes once I was filled in a value with the formula that was there or it is currently in there. But it didn't' worked when I proposed the following formula.
=IF($M748>11,IF(Q712<130000,((Q677-Q560)*$N748),0)),IF($M748<=0,IF(Q712<130000,((Q677-Q560)*$N748),0))
My apology for asking a question that should be considered as part of a new thread. Basically, I'm trying to revamped all the formulas with some modifications so in some cases I can use or put a negative number in Column M. That will save me some good quality time in the future when needed.
Thanks again for your assistance!
Much appreciate it!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.