|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to assign numbers to letters and then create a formula to add them together for a rota
Hi all
I'm a novice at formula and have searched the internet to find out the answer to my question. I have cobbled together a formula, which works but I feel is too long and there must be an easier way of doing it. I read about arrays but do not understand them and I am not sure if it's the right thing to use for this calculation. In brief, I want to be able to add the cells together in rows that contain E (which will equal 7.5) L (which will equal 8) and variations of numbers i.e. 12-8 (which will equal 8). This is an example of what I have put together from copying the first bit from another post. I do not understand what the first bit means, but it does work: =IF(COUNTIF(A13:G13,"="""),"",SUM(COUNTIF(A13:G13, "=L")*8,COUNTIF(A13:G13,"=E")*7.5,COUNTIF(A13:G13, "=9-4")*7,COUNTIF(A13:G13,"=12-8")*8,COUNTIF(A13:G13,"=AD")*7,COUNTIF(A13:G13,"=A L")*7.4)) Thank you for taking the time to read my post, I hope you can help as it will save so much time checking hours for a rota. |
#2
|
|||
|
|||
How about:
=SUM(COUNTIF(A13:G13,{"L","E","9-4","12-8","AD","A L"})*{8;7,5;7;8;7;7,4}) (I assume you really mean there should be a space between A and L). |
#3
|
|||
|
|||
xor, thank you for your reply. I have tried the formula that you've suggested but I can't get it to work.
The A and L should be together as this is the code used for Annual Leave and it should equal 7.4. I sort of understand how the formula works but can you please explain why there are semi colons in the last bit instead of commas? Thanks again for your reply, I will look at it again tomorrow as it's almost midnight and I have an early start :-( |
#4
|
|||
|
|||
Excuse me that I forgot to change some semi colons to commas.
|
#5
|
|||
|
|||
Hi, it worked once I changed the semi colons to commas. Thank you so much it is really appreciated. As I am new on here I don't know how to put the "solved" icon on my post do you know how?
|
#6
|
|||
|
|||
I don't think it is important with this "solved" icon (and I don't know how).
Did you remember to remove that space between A and L (if you do not use the file I uploaded)? |
#7
|
|||
|
|||
I did, and thank you once again
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Swapping column and row formats. Letters to numbers etc. | AdamNT69 | Excel | 2 | 10-17-2016 08:44 AM |
more symbols for super/subscript numbers and letters | djc | Word | 5 | 03-13-2015 10:54 AM |
Captions mixing letters and numbers | trew | Word | 7 | 11-21-2012 12:54 AM |
Creating TOC with letters and numbers | tanababa | Word | 1 | 04-28-2011 01:35 AM |
mailmerge with letters of a different numbers of pages | roel1972 | Mail Merge | 0 | 02-21-2011 11:45 AM |