Tech Support Guy banner
Status
Not open for further replies.
1 - 9 of 9 Posts

· Registered
Joined
·
1 Posts
Discussion Starter · #1 ·
I need a spreadsheet formula that will reference an alternate numerical number in a sum function. I have a column of Priority values 1-22 and I want to assign a reverse value to each priority (i.e. 1=22, 2=21, 3=20, etc.) then I want to post the sum of the values incurred and not the sum of the numerical priority values in the cell at the bottom of the column. Should I be using LOOKUP in conjunction with SUM or SUMIF? Any ideas?
 

· Registered
Joined
·
5,459 Posts
Hi there,

A couple of things right off the bat:

1) Welcome to the board!
2) Please write a moderator and ask to change your username to not have the @ sign (spam bots)
3) Can you give us some examples of your data structure/layout and values?
 

· Registered
Joined
·
2,278 Posts
Is the attached spreadsheet the sort of thing you are looking for?
 

Attachments

· Registered
Joined
·
2,278 Posts
I have also included how you can do subtotals of each priority using SUMIF.

This may help on understanding how to use all the functions you mentioned.
 

Attachments

· Registered
Joined
·
8,565 Posts
I don't know if Villan's suggestion is what you're after. If it is, you can get the same thing without the lookup table by using =ABS(A2-23) in B2 & copying it down. Which is kind of fun :D (if you're sad like me :eek: ).
 

· Registered
Joined
·
2,278 Posts
You sad git Bomb :D :D
 

· Registered
Joined
·
5,459 Posts
I see what you're about Villan, hard to understand the OP sometimes. LOL!

Instead of manually typing them in, how about a reverse rank of your data fields...

=RANK(F4,$F$4:$F$25,0)

This assumes the list will be sorted and in ascending order and having each whole number in the list referenced.
 

· Registered
Joined
·
2,278 Posts
I didn't type them in LOL

I typed 1 & 2 and selected them and copied down
I then typed 22 & 21 and selected them and copied them down.

actually I typed 1 & 2 in one column then 22 & 21 in the next column selected all four cells and copied down.

Just as quick me thinks :)
 
1 - 9 of 9 Posts
Status
Not open for further replies.
Top