A calculation for each letter

Im looking for a formula that gives me a sum for any letter i put in a cell.

Im doing a salary template where 1 cell for each day of the month can be marked with a letter if i get sick..
Im thinking, if i put an X in A1 that means im sick that day, but i still need a sum generated in R1 for that day..
The sickness benefit will be 80% of my hourly salary located in K4..
The 80% can be hardcoded while the hourly rate is adjustable..

Another formula i would like to apply is..
The percentage drops after a certain time you been sick to 75% (Y) and again later to 60% (Z)

Thx

As a start i tried the IF statement...

=IF(OR(A1="o");"o";IF(OR(A1="x");"K4*0.8";""))

..but getting some error for the "0.8" value..

(the "o" value correspons to a zero value which happens the first day of sickness)

1st, you dont need to use OR if you are only testing 1 thing.
=IF(A1="o";"o";IF(A1="x";"K4*0.8";""))

2nd, you need to remove all those "" Anytime you wrap anything inside "" it becomes text, so...
=IF(A1="o";0;IF(A1="x";K4*0.8;""))
(or something like that)

3rd, if you have a bunch of letters and their corresponding values, it might be better to use a small table and then use VLOOKUP to return thevalue you want.

For more direct assistance, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

Hello Ford.. and thx for helping out..

I tried your formula and in some way im making "progress" , im not getting the error about "0.8" im using in the calculation.
All seems good except now im neither getting any results nor any errors from the expected results even if i been trying different things.

You asked for a sample workbook which im attaching plus a short explanation (prefered outcome) on the right side in the workbook.

Im deaply sorry for not being able to give you an attachment handled from my message board, im getting a javascript error (a tiny empty box) even tho i have the latest javascript installed, thats why im giving you a sample of the requested workbook through the link here

Thx /Bertil

Thanks for the file

Can you want me through what you are trying to do?

and by the way, you should try and avoid using merged cells, they cause all sorts of problems with formulas

1st, (if you didnt already know), formulas can only affect the cell they reside in, not any other cells

2nd, for this part...

PHP Code: 

When B10 = "o", then S10 = "o"
When B10 = "x", then S10 = "1360" 


Considering there already is a formula in S10, that would then become...
=if(B10="o","0",if(b10="x",1360,SUM(K10:L10,-M10,-O10)))

The merged cells gives me all other info at the bottom i want out from this worksheet plus to fit when i need it be printed out in A4..
This is only the first half of the worksheet where the final second part is the "outcome" from the first report sheet...

About your formula.. thx.. i tried it and it gave me some sort of error with the ("o","0",if)..
I changed to ("o";"0";if) but then the error moved to ("x",1360,SUM) instead..

And sorry there.. i wasnt clear with why i wanted the sum 1360 in S10.. it comes from cell (I10*L4)*0.8, where 0.8 is hardcoded...
One other thing why i insist on using "o" instead of "0" is that i want a zero value visible in the cell for everybody using the worksheet (without telling everybody to change the settings to visible zero values)..

I can understand why you want "o" (oh) instead of "0" (zero), so no problem there.

I think what went wrong with my formula is the regional settings for Sweden. Try this instead...
=if(B10="o";"o";if(b10="x";(I10*L4)*0.8;SUM(K10:L10;-M10;-O10)))

Note the change from, to ;

Also, you could use Center Across Selection, instead of merging

Hey Ford and thx..

It seems we are coming closer but still not there yet..

I tried the formula =if(B10="o";"o";if(b10="x";(I10*L4)*0.8;SUM(K10:L10;-M10;-O10))) but got an error with the (0.8) again so i changed from dot to comma and the errors where gone but formula is not working, not giving me a value in S10..

But i can see the formula is working because i have a conditional formatting in all S cells (for the attention) and when i remove the formula in S10, S10 gets yellow (but no values), as if excel are putting a zero value there in S10 without the zero.. i hope my explanation make sence..

Why is the hardcoded 0.8 always giving a problem or should it be placed in a cell insted..?

Thx for your patience..
/Bertil

L4*(8/10)?

Hey Ben and thx for helping..

Yes your contribution is an option.. i will use that from now instead..
Sometimes the hardcoded part (0.8) gives an error, then i change to a comma, but then something else disturbing so i have to change it back again to what i used from the beginning..

But anyhow.. formula still not giving any desired values in cell S10..
I have a NAME? error im trying to figure out now..
Could it be the "oh" not being defined as name yet..?

If you upload the revised file, we can take a look at it again

Using: =IF(B10="o",0,IF(B10="x",L4*(8/10),SUM(K10:L10,-M10,-O10))) (in S10:S15, not A10)
I get:

160
1759
2163
3378
2840
2278

Ok!
Thx! I appreciate all help given
Now i've uploaded the intire workbook here

I've been fiddling with this problem i have the whole day and maybe im wrong out here..
Maybe i could add the formula i need in conditional formatting instead..?

Open up my workbook tab "Januari" and you'll see a bunch of numbers, its all only for test..
From B11 to T14 everything is in order, it's all good here..

But! if i get sick i have 80% of full salary..
The sickness benefit is calculated like this - any cell (with a value of course) from range I10:I54 * K4 * (8/10)
The sum of this calculation could be shown either in the the range J10:J54 OR directly in the range RST10:RST54..
One thing to consider here is that all the colored cells are later locked (no access or fiddling with formulas) ONLY white cells are "editable"..

When checking conditional formatting i noticed im not getting a value with numbers, only different colors depending on condition..
Only problem i have then is i cant sum all hours i been sick, depending on what color is shown, to get right sickness benefit..

I only need letters "O" and "X" where "O" represent 0 (zero with no further action) and "X" showing the sum of the desired calculation as explained earlier..
Thats all, nothing fancy..

Thx
/Bertil

How are the numbers in column J:K related to the hours in column I? Your hourly rate is 200 and the hours are ~10 --> 2000 not 3,812.00
?

Hey..

Yes! i'll explain the letters from column A to RST..

A = Put a mark here, either O or X, when im sick. O = no sickness benefit, X = (I * K4 * (8/10)).
B = Date when shift is done.
C = Shift number.
D = Car used when working.
E = Shift start.
F = Shift finished.
G = Pause/break.
H = Sum hours worked accordning clock (with colon).
I = Sum hours worked in decimal form.
J = Cash received.
K = Credits received (Credit cards).
L:M = When working all money, cash and credits handled by the driver will be based on the salary, hence cell K5, but sometimes the driver not get income from some tasks done since those money doesnt belong to either the boss or the driver. Such occasion might be when driving a customer to the airport. Airport has a bar blocking those taxis who not have "prescription" to enter airport area (closer to main entrence). This bar fee/"prescription" price is added to the main price customer pay to get to the airport, and from that price boss withdraws this small fee and pays airport. Its been 37 SEK ($4) aslong as i know.
N:O:P = When we are being hazzled, people running from the tab, we call it "BOM", the sum generated on the meter comes here..
Q = Total sum added from J:K-(L:M)-(N:O:P) on same row EXCLUDING tax.
R:S:T = Total sum added from J:K-(L:M)-(N:O:P) on same row INCLUDING tax.

Boss wants the report receipt generated from the meter everytime when logged out. On this report all prices come INCLUDING tax while drivers salary based on EXCLUDING tax, taxi tax here is 6%, tax money belongs to the state as we all know..

Boss says he is not paying saturdays and sundays and only giving drivers for 8 hours sick per day, thats why the calculations in "Januari" tab look similar in lower group on uploaded worksheet (B18:I21).

My issue/request is that column A can use the letters "O" and "X" to withdraw 20% from daily salary (I * K4) and to be shown in R:S:T cells. My problem is that i cant get the formula right since there in R:S:T cells already "reside" formulas.

Oh sorry i forgot to answer your question Ben.. :/
Its like this.. drivers have 2 salarys at the same time.. based on percentage and hourly.. BUT at the end of the day the driver dont get salary from both, ONLY from 1 based on which one is the highest.. Example: a driver desides to work 8 hours (8 * 200 = 1600) but if the driver "has luck" with a lots of customers, when driver logs out he notice that all money handled by him, cash and credits ends up on lets say 2000 then the salary is based on those 2000 instead of 1600.. each day both percentage and hourly runs parallell.. it makes sence yes..? and as a carrot my boss also gives the difference from the lowest and the highest, can be viewed in latest uploaded worksheet tab "Jan.lönespec." (M:N:O:21).. but thats another story..

/Bertil

Hey..

Yes! i'll explain the letters from column A to RST..

A = Put a mark here, either O or X, when im sick. O = no sickness benefit, X = (I * K4 * (8/10)).
B = Date when shift is done.
C = Shift number.
D = Car used when working.
E = Shift start.
F = Shift finished.
G = Pause/break.
H = Sum hours worked accordning clock (with colon).
I = Sum hours worked in decimal form.
J = Cash received.
K = Credits received (Credit cards).
L:M = When working all money, cash and credits handled by the driver will be based on the salary, hence cell K5, but sometimes the driver not get income from some tasks done since those money doesnt belong to either the boss or the driver. Such occasion might be when driving a customer to the airport. Airport has a bar blocking those taxis who not have "prescription" to enter airport area (closer to main entrence). This bar fee/"prescription" price is added to the main price customer pay to get to the airport, and from that price boss withdraws this small fee and pays airport. Its been 37 SEK ($4) aslong as i know.
N:O:P = When we are being hazzled, people running from the tab, we call it "BOM", the sum generated on the meter comes here..
Q = Total sum added from J:K-(L:M)-(N:O:P) on same row EXCLUDING tax.
R:S:T = Total sum added from J:K-(L:M)-(N:O:P) on same row INCLUDING tax.

Boss wants the report receipt generated from the meter everytime when logged out. On this report all prices come INCLUDING tax while drivers salary based on EXCLUDING tax, taxi tax here is 6%, tax money belongs to the state as we all know..

Boss says he is not paying sickness benefit saturdays and sundays and only giving drivers for 8 hours sick per day, thats why the calculations in "Januari" tab look similar in lower group on uploaded worksheet (B18:I21).

My issue/request is that column A can use the letters "O" and "X" to withdraw 20% from daily salary (I * K4) and to be shown in R:S:T cells. My problem is that i cant get the formula right since there in R:S:T cells already "reside" formulas.


/Bertil

If I understand correctly then perhaps the following formula, pasted into cell R11 and dragged down, will work:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Hello JeteMC and thx for helping out..

I tried your formula and got the same back here as before, a ?NAME error.
I changed it to my region Sweden =IF(A11="";"";IF(A11="x";I11*K$4*0,8;SUM(J11:K11;-L11;-N11))) but still got that ?NAME error.

I've tried to check this but cannot see what you have donw wrong in your formula, for me it looks nice.
One question i have though, did you try your formula in my worksheet and got it working..?

Now when i have fiddled with this issue i have i have another approach in mind..

I know it will be tons of work but.. it will keep me busy..
What if i can add it in my conditional formattings instead..?

I got the corresponding colors working, O = RED and X = YELLOW, formula looks like this LEFT(A11;1)="O" and LEFT(A11;1)="X"
Now i would like to try add a formula only for the "X" value, something like =LEFT(A11;1)="X";I11*K$4*0,8 but this formula is giving me errors maybe because i created it wrong..?

I feel solution is near..

Again, my request:
IF O added in A10 then R10 will be RED with an O (color and O visible).
IF X added in A10 then R10 will be YELLOW and need to calculate I10*K$4*0,8 (color and the sum visible).

Thx
/Bertil

Here is a 2010 version copy of the file with the formula applied.
I also modified the formula in Q10 and down to read: =IFERROR(SUM(R10*0.9434)+(L10*0.0566),"")
Conditional formatting rules are applied as stated in post #19.
Let us know if you have any questions.

Hey JeteMC..

Thx a bunch.. you nailed it.. this is the solution i was looking for..
It was the copy you sent back that worked..

But i see that the formulas are similar except that i used "IF" when i created the formula with the thought that excel wont mind..
Heck, i think i even used "IF" many times before without having any ?NAME problems..

The english translation from "IF" to Swedish is "OM" which is used in the formula now..

Now im going to add the same to the rest of the months..

Thx again everybody involved and to you JeteMC for taking it home..

Well done..
Regards /Bertil

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.