if then greater then less then or inbettween

[bigdom]

I NEED HELP WITH A VACTION CACULATOR. MY COMPANY ISSUSE VACATION MONTHLY AT A RATE OF 16.66 HOURS I CAN ONLY CARRY 320HR ON THE BOOKS THE COMPANY ISSUSES VACATION ERARNED (16.66) AT THE BEGIANING OF THE MONTH AND THEN DEDUCTS THE HOURS USED IN PREVIUOS MONTH SO I STARTED A SPREAD SHEET TO HELP ME KEEP TRACK OF HOURS OF VACATION I NEED TO CONSUME TO AVOID EXCEEDING 320 MARK AND LOSSING VACATION HOURS THE KEY IS TO STAY BELOW 303 IN THE CURRENT MONTH. VACATION CAN ONLY BE USED IN 10 HR INCIMENTS. BELOW IS WHAT I HAVE SO FAR.

ROW 1 BLANK

ROW 2 TITLES OF COLUMNS

ROW 3 BEGAINS THE MONTHLY CALCULATIONS

COLUMN “A” BLANK

COLUMN "B" IS THE MONTH "SEPT"

COLUMN "C" IS ACCURED HOURS 301.12 [ =IF(M3="",G3,N3) ] STARTS ON ROW 4 (NOTE Guy Seela:

If no value is entered in previous month Amount Used (M) then return the value in Projected Balance (G). Otherwise return the value in Balance After Used (N).)

COLUMN "D" IS EARNED HOURS 16.66 MONTHLY

COLUMN "E " BALANCE AFTER EARNED 317.78 [ =SUM(C3:D3) ]

COLUMN "F” AMOUNT REQUIRED TO BE USED IN CURRENT MONTH TO STAY UNDER 303 IN THE FOLLOWING ROW AND SO FORTH THRU THE ROWS [ COLUMN "F" =IF(E1>303,"20","10") ]

COLUMN “G” PROJECTED BALANACE 297.78 =SUM(E3-F3)

COLUMN “H” BLANK

COLUMN “I” BLANK

COLUNM “J” ACCUIRED [ =IF(N3>=L3,L3,N3) ] STARTS ROW 4

COLUNM “K” EARNED 16.66 CONSTIANT

COLUNM “L” BALANCE AFTER EARNED [ =SUM(J3:K3) ]

COLUNM “M” AMOUNT USED CURRENT MONTH ((MANUAL ENTER NUMBER) (INCRIMENTS OF 10 ))

COLUNM “N” BALANCE AFTER USED [ =SUM(L3-M3) ]

SO WHAT I AM LOOKING FOR IN COLUMN “F” AMOUNT REQUIRED TO BE USED IN CURRENT MONTH

GREATER THEN 303 RETURN “20” GREATER 283 THEN BUT LESS THEN 293 RETURN “10” LESS THEN 283 RETURN “0”

 

[kevin9999]

Hi @bigdom and welcome to the board. Here's a generic formula that you should be able to adapt to your needs:

Book1
	A	B
1	304	20
2	290	10
3	282	0
Sheet2

Cell Formulas
Range	Formula
B1:B3	B1	=IFS(A1>303,20,A1>=283,10,A1<283,0)

Also, please note Board Rules #14:
Members should post in a way that is consistent with ‘normal writing’. Users should not use excessive amounts of emoticons, must not SHOUT (use all upper case) or use excessive amounts of punctuation (e.g. ! and ?) in posts or thread titles.

 

[bigdom]

Please forgive me i am total novice with excel
i copied your formula and pasted in in a new book im geting #NAME? as results

 

[Joe4]

Please forgive me i am total novice with excel
i copied your formula and pasted in in a new book im geting #NAME? as results

I don't think he noticed from your profile that you are using Excel 2010, and the IFS function wasn't introduced until Excel 2019.

You could use a nested IF that would be equivalent to his formula, i.e.

=IF(A1>303,20,IF(A1>283,10,0))

But I am confused by your description:

GREATER THEN 303 RETURN “20” GREATER 283 THEN BUT LESS THEN 293 RETURN “10” LESS THEN 283 RETURN “0”

So, what if it is between 293 and 303?

 

[bigdom]

this works for me thank you
anything over 303 +16.66 results in loss of some amount over 320 below 303 +16.66 remainds under the 320 cut off between 293 and 303 the forula gives sresults of 10 which would be the minumum hours required to use.
what the spread shead is designed for is to be able to at a glance
1) let me know how close to the 303 mark i am
2) let me know at a minumum how may hours days vacation i need to take so i dont give any time back to the company
your formula works for me and will accomplish this in my spread sheet
probable much easier and simpler methods ( like take some time off )


in my head it works