Sleeper: Payroll File--Need Help with Formula

[rsrasc]

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]

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]

Hi georgiboy,

Wow! I'm impress. Working great!

Much appreciated.

Regards!

[rsrasc]

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]

Q47:

=IF($M47>11,($N47*Q$5),IF(M$47<=-1,($N47*Q$5),0))

Same logic for Q83

[rsrasc]

Hi georgiboy, thank you again for assisting me. I tested and is working great! Much appreciate it.

[rsrasc]

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]

Do you have a Value in $M748?

[rsrasc]

The value for $M748 should be 12. When I submitted the attachment, I didn't have any values in Column M.

[rsrasc]

The value for $M748 should be 12. When I submitted the attachment, I didn't have any values in Column M.&nbsp;<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]

I suspect the formula failed before because you didn't have a value in M748

[rsrasc]

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]

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]

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]

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]

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!